Want to return latest value of one column when a different column matches a value from another sheet

Paul Higgins

Hi All,

I've been seeing all the great help here, and hoping for some myself. I've been searching, and coming up with mixed approaches. I've been using VLOOKUP to auto-fill a row in a sheet when an ID matches an ID from a master sheet (many:one relationship). Now I also want to update a column in a row in the master sheet where there is a one:many relationship, but only with the value from the latest matching row in the secondary sheet. I got this far with my initial formula, which does return the first match.

=IFERROR(VLOOKUP([Record ID]@row, {Secondary_Sheet_Range}, 6), "New")

I really want the last match based on a date column. I've seen using MAX and COLLECT, etc. Any help would be appreciated. I also have an IFERROR so that it defaults to something if there is no match in the secondary table.

Thanks in advance!

Paul Higgins

I got it working with the INDEX/COLLECT against a Latest Value column... Now the worst part...

Is there anyway to do this from several secondary sheets to the one master? I know... :)

Paul Newcome

You are basically going to want all of your IFERROR functions stacked up at the beginning of the formula.

When you are getting ready to do the second IFERROR, ignore the first one. Forget that there is already an IFERROR function there because really it doesn't matter what functions are in your original formula.

=IFERROR(original_statement, output_if_error)

The original statement is going to be the first IFERROR.

Take a look at this and let me know if that clears things up. I'd be happy to to try explain it a different way if needed.

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(COLLECT({2021 Status}, {2021 Ref}, [Ref]@row, {2021 Latest Value}, 1), 1), INDEX(COLLECT({2020 Status}, {2020 Ref}, [Ref]@row, {2020 Latest Value}, 1), 1), INDEX(COLLECT({2019 Status}, {2019 Ref}, [Ref]@row, {2019 Latest Value}, 1), 1), INDEX(COLLECT({2018 Status}, {2018 Ref}, [Ref]@row, {2018 Latest Value}, 1), 1), ""))))

Paul Newcome

That's exactly it. 👍️

Paul Higgins

I got it working with the INDEX/COLLECT against a Latest Value column... Now the worst part...

Is there anyway to do this from several secondary sheets to the one master? I know... :)

Paul Newcome

You would use what amounts to a nested IFERROR similar to a nested IF.

You have your first INDEX/COLLECT:

=INDEX(COLLECT({Sheet 1}, ............), 1)

If there is no record on sheet 1, then you will get an error, so we wrap that in an IFERROR and use the second portion of that function to run the INDEX/COLLECT on the second sheet.

=IFERROR(INDEX(COLLECT({Sheet 1}, ............), 1), INDEX(COLLECT({Sheet 2}, ............), 1))

Then we say if there is an error with that, run the INDEX/COLLECT on the 3rd sheet.

=IFERROR(original_formula, INDEX(COLLECT({Sheet 3}, ............), 1)

=IFERROR(IFERROR(INDEX(COLLECT({Sheet 1}, ............), 1), INDEX(COLLECT({Sheet 2}, ............), 1)), INDEX(COLLECT({Sheet 3}, ............), 1)

And keep nesting until you have covered all of your sheets.

Paul Higgins

Awesome Paul, thanks! I'll give it a try. I would presume that looking at the most recent sheets first would typically be better performing give I would expect hits there, rather than in historical sheets.

Paul Newcome

Happy to help. 👍️

I would imagine so since the formula will stop running as soon as it finds its first match.

Paul Higgins

I've been reading through some of your other suggestions on similar posts, but I'm coming up a bit stumped. It's a beast of a formula, and I just don't think I have the IFERROR logic understood. Here is my formula I'm building, but the nesting is obviously not correct. My thought process is do the first formula, and on error, do the second, and so forth. Not right apparently.

=IFERROR(INDEX(COLLECT({2021 Status}, {2021 Ref}, [Ref]@row, {2021 Latest Value}, 1), 1), 

IFERROR(INDEX(COLLECT({2020 Status}, {2020 Ref}, [Ref]@row, {2020 Latest Value}, 1), 1),

IFERROR(INDEX(COLLECT({2019 Status}, {2019 Ref}, [Ref]@row, {2019 Latest Value}, 1), 1),

IFERROR(INDEX(COLLECT({2018 Status}, {2018 Ref}, [Ref]@row, {2018 Latest Value}, 1), 1), ""))))

Paul Newcome

You are basically going to want all of your IFERROR functions stacked up at the beginning of the formula.

When you are getting ready to do the second IFERROR, ignore the first one. Forget that there is already an IFERROR function there because really it doesn't matter what functions are in your original formula.

=IFERROR(original_statement, output_if_error)

The original statement is going to be the first IFERROR.

Take a look at this and let me know if that clears things up. I'd be happy to to try explain it a different way if needed.

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(COLLECT({2021 Status}, {2021 Ref}, [Ref]@row, {2021 Latest Value}, 1), 1), INDEX(COLLECT({2020 Status}, {2020 Ref}, [Ref]@row, {2020 Latest Value}, 1), 1), INDEX(COLLECT({2019 Status}, {2019 Ref}, [Ref]@row, {2019 Latest Value}, 1), 1), INDEX(COLLECT({2018 Status}, {2018 Ref}, [Ref]@row, {2018 Latest Value}, 1), 1), ""))))

Paul Higgins

Got it!!!!

=IFERROR(

IFERROR(

IFERROR(

IFERROR(

INDEX(COLLECT({2021_FSRs CIT Status}, {2021_FSRs CIT Ref}, [CIT Ref]@row, {2021_FSRs Latest Value}, 1), 1), 

INDEX(COLLECT({2020_FSRs CIT Status}, {2020_FSRs CIT Ref}, [CIT Ref]@row, {2020_FSRs Latest Value}, 1), 1)),

INDEX(COLLECT({2019_FSRs CIT Status}, {2019_FSRs CIT Ref}, [CIT Ref]@row, {2019_FSRs Latest Value}, 1), 1)), 

INDEX(COLLECT({2018_FSRs CIT Status}, {2018_FSRs CIT Ref}, [CIT Ref]@row, {2018_FSRs Latest Value}, 1), 1)),

, ""

)

Paul Newcome

That's exactly it. 👍️

Paul Higgins

When you finally think clearly and break it down to this or that, it becomes clearer. Useful examples in some of the other threads that helped me get there, along with your help!