Index,match,match

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.

Find more posts tagged with

Formulas

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.