Multi-select fields with INDEX/MATCH (or COLLECT) with multiple values

brian.gotti · 07/23/20

Hello fellow Smartsheet users!

I am running into unique problem and more importantly finding the way to do something within Smartsheet. A little background. I build a request intake/workflow management tool for my Marketing department at a global organization. Users make Design, Content, Web, and Video requests through this tool.

My Finance department has requested that on requests that meet certain criteria a accounting code get generated and attached to the request. It consists of the following:

Account (varies based on the details of the request)

Cost Center (for the sake of conversation in this post, this is a Sales Channel)

Region (this is what I need help with)

The Account code really was no sweat. I used a series of nested IF functions to determine the type of request, then the variables that determine the final account code that needs to be assigned. That easy.

Cost Center, that is a pretty simple solve as well, as it's determined again by user input in the request form (they don't even realize they are providing it).

Region is where this gets tricky and where I need help. My Finance team has a list of cities that are grouped by regions. The requester indicates where the requested asset from our Design team is going to be leveraged that they are requesting. The thing here is a requester can multi-select cites and those cities may cross regional boundaries, and then the Finance team wants to know what all regions the request falls in.

I began to try and figure this out, but got nowhere. Here is a sample grid and example data to help everyone understand what I'm trying to accomplish.

Example:

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

Though I know it will not provide the result I'm looking to achieve my current formula in the "Output" column is the follwing:

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

This is where I got, but doesn't get me what the ideal outcome is. Ideally we need the following:

Row 1 - Desired output would be "Region 1" and "Region 2" in the output field as two values.

Row 2 - This is actually accurate based on the desired outcome. "City 1" is the only selected city and that is in "Region 1", so this is fine.

Row 3 - Desired output would be "Region 2" only, as both cities that are selected are in the same region. We do not want duplicate entries in the results.

Does anyone have any ideas or any questions that I might be able to answer to help you help me?! 😂

Paul Newcome · 07/23/20

Try this...

=JOIN(DISTINCT(COLLECT(Region:Region, City:City, CONTAINS(@cell, Input@row))), ",")

Paul Newcome · 07/23/20

Are you able to set up a table that has each city in one column and the corresponding region in another column? If so, you could use a JOIN/DISTINCT/COLLECT.

brian.gotti · 07/23/20

I do have a test grid setup:

Screen Shot 2020-07-23 at 9.36.19 AM.png

That is the table I was using to INDEX/MATCH. I just tried to insert this formula:

=JOIN(COLLECT(DISTINCT(Test:Test), [Primary Column]:[Primary Column], HAS([Primary Column]:[Primary Column], Formula@row)))

I get #INCORRECT ARGUMENT SET as a result.

Paul Newcome · 07/23/20

The COLLECT should be nested inside of the DISTINCT.

=JOIN(DISTINCT(COLLECT(Test:Test, [Primary Column]:[Primary Column], CONTAINS(@cell, Input@row))), "delimiter")

brian.gotti · 07/23/20

Okay, so if I'm understanding this correctly, it would be something like this:

=JOIN(DISTINCT(COLLECT(Region:Region, City:City, CONTAINS(Input@row, City:City))), ",")

I have the above formula in the "Output" column, using this test Grid. It returns a blank cell.

Screen Shot 2020-07-23 at 10.31.16 AM.png

Paul Newcome · 07/23/20

Try this...

=JOIN(DISTINCT(COLLECT(Region:Region, City:City, CONTAINS(@cell, Input@row))), ",")

Trang Nguyen · 10/12/22

@Paul Newcome I found a similar solution you provided in another post and was asking if you could help me with some follow-up questions to your solution. I've provided the formulas I'm using I got from the other post which works except for my questions.

1. What edits to your formula can I make if I have an INDEX row with no MATCH and just want the INDEX word to appear or put a blank in its place so the output lines up with the @cell?

The multi drop Workstream Dependencies field (bottom 3 screen shots aka source sheet) is a combination of a Primary Workstream (WS) and a Sub-Workstream, but some Primary WS's don't have sub WS.

2. What if you have 3+ drop downs chosen?

In order to do roll-up reporting at the Primary Workstream and Program levels, I have to take the WS field in the resource sheet above and split the Primary and Sub Workstream. The output is not aligning with the drop down choices because "Call Center" doesn't have sub WS. 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) but put a placeholder there so the wrap text lined up the text with the Dependency. Any thoughts?