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

07/23/20
Accepted

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:



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([email protected], [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?! 😂

Best Answer

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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.

    thinkspi.com

  • brian.gottibrian.gotti ✭✭✭✭✭

    I do have a test grid setup:



    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], [email protected])))


    I get #INCORRECT ARGUMENT SET as a result.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    The COLLECT should be nested inside of the DISTINCT.

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

    thinkspi.com

  • brian.gottibrian.gotti ✭✭✭✭✭

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


    =JOIN(DISTINCT(COLLECT(Region:Region, City:City, CONTAINS([email protected], City:City))), ",")


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



Sign In or Register to comment.