Cross Sheet Use of IF / VLOOKUP / Index Collect

CCChmura

Hi all, I have been searching for the right answer to this issue I am having without success.

I have a sheet that is pulling in reference cells from other sheets and I am trying to reference the most recent value from the source or database sheets. I can pull them in with index / match or Vlookup but struggling to pull the MOST RECENT.

The closest I have come is in the database I created 2 helper columns that looked for the most recent date and scored it as a 1 if they matched. Now trying to figure out how i reference that most recent row value in the reference sheet.

I have tried using If and Vlookup - fail … and no matter what I do with Index collect I continue with #UNPARSEABLE

Happy to share sheets or screenshots

Below are what I have tried, Index match works fine but does not account for the most recent data in the reference sheets.

=IF({Most Recent} = 1, VLOOKUP(Name@row, {Check-in Range}, 2, false), "---")

=INDEX(COLLECT({NPDS Orientation Check-In Range 2}, {Most Recent}, 1, {RN},Name@row),,3)

"NPDS Orientation Check-In Range 2" = the source sheet range including all the associated columns , where "Most Recent" & "RN" are only the impacted ranges within the 1st larger range.

Thoughts ?

~Chris `

SSFeatures

Hi @CCChmura,

Try a formula like this:

=INDEX(Value:Value, MATCH(MAX([Modified By]:[Modified By]), [Modified By]:[Modified By], 0))

This uses MAX to find the largest value in the Modified By column, which is the most recently modified row.

Then it uses MATCH to find the row for that value.

Then it uses INDEX to get value from the "Value" column.

You'll need to update this formula to use your cross sheet references, but it should work!

Best!

Paul Newcome

Your INDEX/COLLECT is pretty close. Just a couple of adjustments to the end of it…

=INDEX(COLLECT({NPDS Orientation Check-In Range 2}, {Most Recent}, 1, {RN},Name@row), 1)