Contents of a Dropdown Multi Select Column

Anil Varma

Hi All

I need help in using the contents of a Dropdown Multi Select Column. The column has the following options

A

B

C

I want to use the actual content say AB to lookup another Smartsheet for a price for that selection. I am sorry if there is an obvious answer as I can’t find it.

Thanks

AV

Paul Newcome

For #1: The section immediately after CHAR(10) is where you specify whatever delimiter you are using in your table. So where you see .............CHAR(10), " ").............. in the below, the space between the quotes is where you specify what you are using.

=INDEX([Column5]:[Column5], MATCH(SUBSTITUTE([Column2]@row, CHAR(10), " "), [Column4]:[Column4], 0))

The logic: The SUBSTITUTE function replaces the multi-select column delimiter of a line break with whatever you want to use in your lookup table. We then use that as the search criteria for the MATCH function which searches [Column4] and generates a row number. That row number tells the INDEX function which row to pull the data from in [Column5].

For #2: You can control how the answers appear on the sheet regardless of the order they are selected in. I gave more detail in my last post:

"As for which order the selections are made in for the multi-select dropdown...

Regardless of which order they are chosen in, they will always appear in the same order as they are populated in the column properties. So if your column properties are

A

B

C

And someone selects A and B, then it will always be in the order of

A B

even if they selected B first and then A."

Paul Newcome

Can you provide a screenshot of how your price list is laid out? Sensitive/confidential information can be removed, blocked, and/or replaced with "dummy data" as needed.

Anil Varma

Hi Paul

Thanks for responding.

I have kept this very simple - ideally I need to have the selection separated by say a slash or similar and then do a look up. Also need to understand what happens if the user selects B and then A versus A and then B.

Anil Varma

Paul Newcome

I assume your price list is columns 4 and 5? If that is the case, you will need to use line breaks in between the letters. Line break is the delimiter for multi-select columns.

If you do not want to enter line breaks into your price listing, you would need to remove them before doing the lookup.

I cannot tell based on your screenshot if you have a space in between A and B on your price list.

If you do, try this...

=INDEX([Column5]:[Column5], MATCH(SUBSTITUTE([Column2]@row, CHAR(10), " "), [Column4]:[Column4], 0))

If you do not, try this...

=INDEX([Column5]:[Column5], MATCH(SUBSTITUTE([Column2]@row, CHAR(10), ""), [Column4]:[Column4], 0))

As for which order the selections are made in for the multi-select dropdown...

Regardless of which order they are chosen in, they will always appear in the same order as they are populated in the column properties. So if your column properties are

A

B

C

And someone selects A and B, then it will always be in the order of

A B

even if they selected B first and then A.

Anil Varma

Paul

Thank you - perfect! (Not sure of the logic)

1. Can I remove the spaces and insert a slash or dash - helps with the Lookup.
2. As I cannot control the order of selection (at least 5 options) by the user, can I manipulate the answer so it is always in the some kind of order. Alternatively can I use the "OR" function to create a "helper" column which can help with the Lookup.

Really appreciate your help with the first part - if the second part can be done, with your help I am there!

Anil Varma

Paul Newcome

For #1: The section immediately after CHAR(10) is where you specify whatever delimiter you are using in your table. So where you see .............CHAR(10), " ").............. in the below, the space between the quotes is where you specify what you are using.

=INDEX([Column5]:[Column5], MATCH(SUBSTITUTE([Column2]@row, CHAR(10), " "), [Column4]:[Column4], 0))

The logic: The SUBSTITUTE function replaces the multi-select column delimiter of a line break with whatever you want to use in your lookup table. We then use that as the search criteria for the MATCH function which searches [Column4] and generates a row number. That row number tells the INDEX function which row to pull the data from in [Column5].

For #2: You can control how the answers appear on the sheet regardless of the order they are selected in. I gave more detail in my last post:

"As for which order the selections are made in for the multi-select dropdown...

Regardless of which order they are chosen in, they will always appear in the same order as they are populated in the column properties. So if your column properties are

A

B

C

And someone selects A and B, then it will always be in the order of

A B

even if they selected B first and then A."

Anil Varma

Paul

Thank you - really appreciate your help - all works fine.

I can see great advantages in using Smartsheet. I do find the training and reference material limited in allowing me to make a quick start. Except for your help, I am learning from trial and error - this forum is very helpful.

As an aside, I have realised using an iPad in the UK for Smartsheet is not a good idea. Apple has decided to make inverted commas different from the US Keyboard and as a result I experienced a frustrating time wondering what I was doing wrong with my formulas. No problems with Excel however - I think Smartsheet need to look into this.

Sincerely

Anil Varma

Paul Newcome

Happy to help! 👍️

Paul Newcome

As for the issue with the keyboard, feel free to either reach out to support or submit a product enhancement request. They may have some insight into that.