INDEX(MATCH(COLLECT with most recent data

System

This discussion was created from comments split from: Help with INDEX MATCH COLLECT formula.

Jen Nguyen

Hello @Paul Newcome Or anyone,

I am having the same issue... I have data entered into the Audit Sheet "Gemba Board Walk Audit List 2.0" with columns: "Venue" and "Date". Please see screenshot. You'll see the first column "Gemba Walk" which I created to combine Venue and Date as a unique identifier.

What I need to do is, on a separate sheet "Summary Sheet" to return data for each specific venue with the most recent data. For instance, Valet's latest data was 2/5/20, so the summary sheet that I am working on will return [all] data form that specific date and venue.

I tried to use the formula from the original poster but it's a bit confusing.

Here's what I have so far and it's not working...

=INDEX({Gemba Board Walk Audit List 2.0 Range 1}, MATCH(MAX(COLLECT({Gemba Board Walk Audit List 2.0 Range 1}, { Gemba Board Walk Audit List 2.0 Range 3}, [Gemba Board Walk Audit List 2.0 Range 2]@row)), { Gemba Board Walk Audit List 2.0 Range 3}, 0))

Jen Nguyen

So I was able to retrieve the latest date for that specific venue..

=MAX(COLLECT({Gemba Board Walk Audit List 2.0 Range 3}, {Gemba Board Walk Audit List 2.0 Range 4}, "Buffet"))

But now I need each column on the summary sheet to pull data from its entire row out of the "Gemba Board Audit List 2.0" sheet

I tried this formula on Excel and seems to work but it doesn't work on Smartsheet:

=INDEX({Gemba Board Walk Audit List 2.0 Range 7}, MATCH([Latest]1 & [Venue]1, {Gemba Board Walk Audit List 2.0 Range 3} & {Gemba Board Walk Audit List 2.0 Range 4}, 0))

Genevieve P.

Hi @Jen Nguyen

Before diving into these formulas, can I clarify the reason you're looking to do this in another sheet?

If you're looking to return the entire row, based on the latest date, what about creating a Report to pull this information, instead (see here).

You've done a great job with the MAX formula... instead of having this in a cross-sheet formula, you could have the Max Date for each Venue calculated in a Sheet Summary field on your sheet:

=MAX(COLLECT(Date:Date, Venue:Venue, "Buffet"))

Then, as you can see, I've set up a helper column to automatically check the box if the date in the sheet is the Max Date for that specific Venue:

You could create a Report from this sheet that uses the Criteria if the box in that column is checked. Then it would automatically pull through the whole row associated with that box (although you can choose how many of the columns to show).

The formula I used as an example is as follows (I've adjusted the Date column name to be yours in this text):

=IF(AND(Date@row = [Buffet Max Date]#, Venue@row = "Buffet"), 1, IF(AND(Date@row = [Valet Max Date]#, Venue@row = "Valet"), 1))

It looks to see if the Date in that row is the same as the Max Date for Buffet, AND also if the Venue in that row is Buffet. If it is, it will check the box. If neither of those are correct, it will move on to the next statement, which is now looking for Valet.

=IF(AND(Date@row = [Buffet Max Date]#, Venue@row = "Buffet"), 1,

IF(AND(Date@row = [Valet Max Date]#, Venue@row = "Valet"), 1))

You can just replace the Sheet Reference cell [Buffet Max Date]#, with the new Venue's sheet summary cell. Then replace the word between the "quotes" with the new venue to check for.

If you go this route and are having trouble with the formula, please post what you have so far and I'd be happy to help!

Let me know if you think this Report option will work for you.

Cheers,

Genevieve