Nesting JOIN?, IF and INDEX(MATCH

Hello!

Longtime lurker, first time poster. Trying to create a formula that will cross-check data in a cell then check a series of IFs to pull certain data from another sheet into a comma-limited cell. I've managed to get individual IF statements to pull correctly from the other sheet, checking appropriate information. Here's that section:

=IF(CONTAINS("Priority 1", [Priorities]@row), INDEX({INFO Priorities 1}, MATCH([Name]@row, {INFO Name}), 0))

However, I'm not sure how to achieve the joining of multiple cells for this. As in, I would like the formula to check if the ref cell has more than one Priority (there are 5), and return the ones it fines, like (Priority 1, Priority 3, Priority 5). I can make it work by use IF + IF, but it won't delimit (that I can figure out, anyway) and ends up a bash of words.

Is this possible with JOIN? I tried using this:

=JOIN(IF(CONTAINS("Priority 1", [Priorities]@row), INDEX({INFO Priorities 1}, MATCH([Name]@row, {INFO Name}), 0)), IF(CONTAINS("Priority 2", [Priorities]@row), INDEX({INFO Priorities 2}, MATCH([Name]@row, {INFO Name}), 0)), ",")

But it returned Incorrect Argument.

Thank you in advance for any advice!

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

So…. this works, but hopefully someone else will come along with a more elegant solution. The only way I know to make this work is to manually build up the joins instead of using the JOIN() function.

=IF(CONTAINS("Priority 1", [Priorities]@row), INDEX({INFO Priorities 1}, MATCH([Name]@row, {INFO Name}), 0), "") + IF(AND(CONTAINS("Priority 1", [Priorities]@row), CONTAINS("Priority 2", [Priorities]@row)), ", ",
"") + IF(CONTAINS("Priority 2", [Priorities]@row), INDEX({INFO Priorities 2}, MATCH([Name]@row, {INFO Name}), 0), "") + IF(AND(OR(CONTAINS("Priority 1", [Priorities]@row), CONTAINS("Priority 2", [Priorities]@row)), CONTAINS("Priority 3", [Priorities]@row)), ", ", "") + IF(CONTAINS("Priority 3", [Priorities]@row), INDEX({INFO Priorities 3}, MATCH([Name]@row, {INFO Name}), 0), "") + IF(AND(OR(CONTAINS("Priority 1", [Priorities]@row), CONTAINS("Priority 2", [Priorities]@row), CONTAINS("Priority 3", [Priorities]@row)), CONTAINS("Priority 4", [Priorities]@row)), ", ", "") + IF(CONTAINS("Priority 4", [Priorities]@row), INDEX({INFO Priorities 4}, MATCH([Name]@row, {INFO Name}), 0), "") + IF(AND(OR(CONTAINS("Priority 1", [Priorities]@row), CONTAINS("Priority 2", [Priorities]@row), CONTAINS("Priority 3", [Priorities]@row), CONTAINS("Priority 4", [Priorities]@row)), CONTAINS("Priority 5", [Priorities]@row)), ", ", "") + IF(CONTAINS("Priority 5", [Priorities]@row), INDEX({INFO Priorities 5}, MATCH([Name]@row, {INFO Name}), 0), "")

cpentoney

This does indeed work, but holy cannoli it's about a half page long. Hahaha. Thank you! I do wonder if there's a more elegant solution as well.

Carson Penticuff

Happy to help. I am going to mark this thread and come back to it later to see if I can come up with something better.

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.