Multi-select fields with INDEX and MATCH (Multiple value output)

Hello fellow Smartsheet users!

I am trying to do something I THINK is unique, but don't know IF it is possible. I am building an intake/work/request management tool for my Marketing department at a global company. My Finance department through me a curve ball, and wants accounting information attached to certain requests.

My Finance Team need the following on the applicable requests:

Cost Center (Essentially a Sales Channel for the sake of this post)

Account Number

Region

I was 100% able to have it identify what requests needs it, and based on criteria and nested IF formulas define Cost Center and Account Number. The Region is the tricky one.

The requester is able to multi-select values for where the asset being requested is essentially going to be used. They want Region in the accounting to be contingent on what value(s) are selected for asset destination.

Here's the tricky part, this is not a 1-1 match. So I can't just INDEX/MATCH against a reference table. They want to know if two (or more) options are selected what Region they fall in without duplication of the Region in the output, and that if they cross regional bounds, and they want the output to list all applicable regions without again without duplicates.

See example table here:

Screen Shot 2020-07-22 at 4.44.17 PM.png

Right now, I'm using the following formula in the Output column.

=IFERROR(INDEX([Index Col]:[Index Col], MATCH(Input@row, [Match Col]:[Match Col], 0)), "Multi-Regional Request")

Ideally, the expectation being that "City 1" and "City 2" are selected in row one so the output would be "Region 1" and "Region 2" in the Output.

Row two is working fine, obviously only one value is selected.

Row three has "City 2" and "City 3" selected, so the current expectation is that Output populate with only one instance of "Region 2".

ANY guidance here would be greatly appreciated!!!!

Find more posts tagged with

Accepted answers

All comments

Mathieu PERSICO

Hi Brian,

Working with multiple-choice dropdown columns is tricky.

I think you should use the JOIN & COLLECT functions instead of INDEX & MATCH.

Here is what I got :

Here is the formula :

=JOIN(DISTINCT(COLLECT([Index Col]:[Index Col], [Match Col]:[Match Col], CONTAINS(@cell, Input@row))), SUBSTITUTE($LineBreak$1, "-", ""))

Careful here : In the $LineBreak$1 cell, you have to write two dashes in two different lines ( "-", then CTRL+ENTER, then "-") as shown. This will enable the separation of your multiple values in your Output column.

Hope this helps!

Have a good day,

Trang Nguyen

@Mathieu PERSICO This is AMAZING! I have 2 questions.

What if you have an INDEX with no MATCH and just want the INDEX word to appear?
What if you have 3+ drop downs chosen?

In reference to #1, I am using your formula in 2 fields. The multi drop down is the Workstream Dependencies field that is a combination of a Primary Workstream and a Sub-Workstream. In order to do some roll-up reporting at the Primary Workstream and Program levels, I have to split the Primary and Sub Workstream once they are chosen. The output is not aligning with the drop down choices because "Call Center" doesn't have sub-Workstreams. I have a number of workstreams that don't have sub Workstreams that roll-up under it. I want to be able to either leave that line blank (if possible). I tried putting text in the empty cells to read (Primary WS), but it's still not appearing in the output fields. Any thoughts?

Thanks in advance!

Mathieu PERSICO

@Trang Nguyen Glad to help!

There is an easy solution to get a missing match from the JOIN+COLLECT solution but we can work it out.

Solution 1: How big are the Primary WS and Sub WS lists? You can use a helper column to find out when there is a new entry value not found in your MATCH list such as:

and then complete your list with the missing values.

Solution 2: Using an API to auto-complete the missing values (if that's an option for your case)

Would that help?

For further details, you can contact me: https://www.linkedin.com/in/mathieu-persico-ph-d-35877b55/

Have a great day,

Mathieu PERSICO

Trang Nguyen

This may work. Let me play around with it and get back to you. Solution 2 could work but is more involved as we would have to involve our IT department. Thanks so much!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.