Returning results for identifier as a single identifier or within a join from the same column

Andrea_F

Hello! I have a complicated formula that I need help writing. I have one sheet with three columns, Column A is the 'Created' column, Column B is the score given via form, Column C is either a single identifier or joined identifiers based on the form selection. I want to pull the latest score into another sheet which lists each identifier as a single record. So the formula needs to recognize the Max date as well as the identifier that may be contained within a join or a single identifier. Any gurus out there that want to take this one.

Andrea_F

I ended up adding a help column of joined identifiers and basically saying IFERROR (NOMATCH) on the single identifier, then use the new joined column, and IFERROR still " ". Here is the formula:

=IFERROR(INDEX(COLLECT({Column B}, {Column C}, [Single Identifier]@row, {Column A}, MAX(COLLECT({Column A}, {Column C}, [Single Identifier]@row))), 1), IFERROR(INDEX(COLLECT({Column B}, {Column C}, [Joined Identifier]@row, {Column A}, MAX(COLLECT({Column A},{Column C}, [Joined Identifier]@row))), 1), ""))

Genevieve P.

Hi @Andrea_F

Thanks for posting your solution! 🙂