Return Value from List

I have a sheet with a list of non-distinct master ID numbers that are grouped into "Group 1" through "Group 6" based on a distinct secondary ID. I am trying to write a formula to grab the greatest priority value if there are differences within the same master ID. My formula is in the correct priority order. Here is what I am using but getting an invalid operation error:

=IF(COLLECT({Group #}, {Master ID}, [MASTER_ID]@row ) = CONTAINS("Group 1", @cell ), "Group 1", IF(COLLECT({Group #}, {Master ID}, [MASTER_ID]@row ) = CONTAINS("Group 2", @cell ), "Group 2", IF(COLLECT({Group #}, {Master ID}, [MASTER_ID]@row ) = CONTAINS("Group 6", @cell ), "Group 6", IF(COLLECT({Group #}, {Master ID}, [MASTER_ID]@row ) = CONTAINS("Group 4", @cell ), "Group 4", IF(COLLECT({Group #}, {Master ID}, [MASTER_ID]@row ) = CONTAINS("Group 3", @cell ), "Group 3", IF(COLLECT({Group #}, {Master ID}, [MASTER_ID]@row ) = CONTAINS("Group 5", @cell ), "Group 5", ""))))))

Sheet is set up like this:

I want it to return Group 1 for 5897 and Group 2 for 5899. Is there a way to fix my formula to do this? Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

I would suggest a helper column there in the main sheet that extracts the group number and outputs it as a value:

=VALUE(RIGHT([Group #]@row))

Then in the sheet you want to pull the value to, you would use something along the lines of:

="Group " + MIN(COLLECT({Helper Column}, {Master ID}, @cell = [MASTER_ID]@row))

kibr

Thank you for your answer! I think I am following. I've got the helper column set up and tried the second formula but am still getting an error and not sure that it is iterating through the list in the order I need it to. Any thoughts? I appreciate the help.

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.