Search and find the last sequential duplicate key in a Smartsheet?

mtuccillo

I have a target sheet that has duplicate keys where the keys/rows can be found throughout the Smartsheet. I need to display the column named 2501_January value from the last sequential key's row in the target sheet.

In this example, I want to search and find the last Row Key Rev-2 at the bottom of the Smartsheet from my source sheet. So, the 2501_January column's $350.00 would be the value that I need to display in my source sheet.

In my source sheet I have the following formula in a column named, "Helper":

=INDEX({2501_January}, MATCH([Row Key]@row , {Row Key}, 0))

The issue I am experiencing is that the formula finds the first sequential Rev-2 (Row Key) instance in the target Smartsheet and displays incorrectly $600.00 instead of the $350.00 which is on the last sequential row (see example screen capture).

Is there a way to read the last sequential Row Key in my target sheet so that I can display the correct $350.00?

Thank you

Paul Newcome

Try this:

=INDEX(COLLECT({2501_January}, {Row Key}, @cell = [Row Key]@row), COUNTIFS({Row Key}, @cell = [Row Key]@row))