Lookup help?

cainservicestn

I'm not sure where to start or if this is possible, but here goes..

I have a "Catch Log" that is recording fish caught (each row being 1 fish), and a "Metrics" sheet which is housing formulas to determine the metrics for the winners of a fishing tournament. I have the metrics formulas figured out, but is there a way to "lookup" the Team Name associated with the winner's metrics?

This is a copy of the test Catch Log:

This is a copy of the test Metrics sheet:

I would like to populate the "Team Name" in the Metrics sheet with the "Team Name" from the Catch Log with the corresponding metric.

Adam Murphy

Try something like this:

=INDEX({Team Name}, MAX(COLLECT({Weight}, {Age Group}, [Age Group]@row)))

Hope this helps!

cainservicestn

https://community.smartsheet.com/discussion/comment/477812#Comment_477812

It's coming back as Invalid Data Type.

From "MAX" onward is the exact formula I have in the "Value" column of my metrics sheet. Where "Weight" and the first- "Age Group" are referencing the information on the Catch Log. So I added your suggestion with the "Team Name" also referencing the catch log team name.

Have I got something mixed up with my references?

Paul Newcome

Try this:

=INDEX(COLLECT({Team Name}, {Age Group}, @cell = [Age Group]@row, {Reference Column}, @cell = Value@row), 1)

The {Reference Column} cross sheet reference will actually be different references depending on the exact data point. For example, you would want to reference the {Weight} column for biggest fish, but you would need a separate cross sheet reference in that spot of the formula referencing the {Net Weight} to pull the heaviest net.

Adam Murphy

Paul is correct.