Index,match,match

Jesus-LLTK

I'm having the hardest trouble figuring this part out. Even after doing some searching and people asking for the same thing. When i try to use it on my sheet it doesn't work.

I want to do excel's version of index,match,match. I've tried index(collect), join(collect), max(collect), but i am doing something wrong apparently.

I have a bunch of sheets where i'm trying to see the last location of an item. Each sheet can enter the same item. In a item finder sheet i have a column that you can enter the item and based on the formulas it will tell me if the item exists in those sheets by doing references. I need to retrieve the created(date) column using formulas, i have 2 criteria.

The formula i'm trying to do is

=JOIN(COLLECT({Created(date)}, {Item List}, Item@row, {Created(date)}, MAX({Created(date)})))

or probably best described like so

=Max(Index([Created(date):Created(date)],match(Item@row,{Item List},0),0))

the join(collect) worked good at first, but when i enter more information the formula results from the others go away and the newest entry pops up.

With my 2 criteria i want my end results to show the last time they were entered in any sheet and for the value to always stay on screen.

Jesus-LLTK

More information:

=JOIN(COLLECT([part 1]1:[part 1]11, Time1:Time11, MAX(Time1:Time11), formula1:formula11, formula@row))

My biggest problem with doing join(collect)) is only 1 of them gets the answer. I want both 1 and 2 to show a timestamp. But what happens is which ever formula has the highest time value thats the one that shows. the other goes blank.

so how can i make 1 and 2 show a timestamp of the max value in part 1, using formula column as the criteria.

answer:

1=10:55 AM

2=10:56AM

David Joyeuse

Hi @Jesus-LLTK

I'm not sure I understood well what you're trying to achieve here, but in your formula you use MAX(Time1:Time11) as a criteria. This will always return 09/19/20 10:56 AM, whatever is in the formula column. Thus only the last one works, because 2 is the only formula that as already the MAX date.

If what you want to do is return the latest date for each formula value, it should be like this:

=MAX(COLLECT([Part 1]$1:[Part 1]$11, [Formula]$1:[Formula]$11, [Formula]@row))

This will return the latest date for each value in the formula column.

Now if you want to display this in your part 2 column, just do this:

= [Formula]@row + " = " + MAX(COLLECT([Part 1]$1:[Part 1]$11, [Formula]$1:[Formula]$11, [Formula]@row))

I'm not sure JOIN will work as it'll probably won't recognize the MAX(COLLECT(...)):[Formula]@row as a range, but I could be wrong. Also JOIN requires the range cells to be next to each other, so that may force you to change columns' order as well.

Hope it helped!