Lookup help?

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.

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.