Return Adjacent Column Value of a Filtered Item

Hello experts,

I am trying to figure out how to display the Status of the Primary Column after the largest Data Point has been determined.

The formula I am using under "Largest Data Point" is:

=MAX(COLLECT([Data Point]2:[Data Point]7, [Primary Column]2:[Primary Column]7, [Primary Column]@row))

So this tells me the largest numbered data point for the given Primary. Looking at the Raw Data, A has multiple entries and the largest Data Point of the 2 entries is 3.

What is the easiest way to show the Status of that row where it has A/3/Y?

I've tried to use VLOOKUP with the MAX(COLLECT()) as the search_value, but the problem I ran into was when there was multiple Data Points (take A/3/Y and D/3/X for example), it would only return the 1st row that came up having the 3 since it was the search criteria. In the example posed above, you would result with A/3/Y but also D/3/Y instead of D/3/X if that makes sense.

Please help. Thanks!

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!