Index Match formula pointing to Multi Select Range

DanR

I am using an Index/Match formula to pull contact info from a Team List smartsheet into a Schedule smartsheet. For the most part it works just fine. In the Team List smartsheet I have a column named "Trade" set up as a Multi Select dropdown. In the Schedule smartsheet I have a column also named trade and it is set up as a Single Select dropdown. The Index/Match formula uses the Trade column in the Schedule to look for a match in the Team List Trade column and return the data from another cell on that line. It works well if only one selection was made in the Trade column in the Team List. If more than one selection was made on the Team List, the formula returns "#NO MATCH." This is the Index/Match formula I'm using: =IF(ISBLANK(Trade@row), " ", INDEX({Sub}, MATCH(Trade@row, {Trade}, 0))). Changing the search Type results in incorrect info.

Can this formula be tweaked to work, or is there another formula type that will work better in this scenario?

Genevieve P.

Hi @DanR

If you're looking into a Multi-Select column for one selection (either on its own or with other selections) then you will want to incorporate the HAS function into your formula. This tells it to look to see if the cell has this selection, versus looking for an exact match of the single selection.

Additionally, it sounds like you may have multiple instances of this Trade value in the other sheet, which means there could be multiple "Sub" values to return. You may want to try using a JOIN(COLLECT formula in this instance instead, so that if there are more than one selections you can see them all in one cell.

Try this:

=IF(ISBLANK(Trade@row), " ", JOIN(COLLECT({Sub}, {Trade}, HAS(@cell, Trade@row)), " / "))

Let me know if this makes sense and if it works for you!

Cheers,

Genevieve

Genevieve P.

Hi @DanR

If you're looking into a Multi-Select column for one selection (either on its own or with other selections) then you will want to incorporate the HAS function into your formula. This tells it to look to see if the cell has this selection, versus looking for an exact match of the single selection.

Additionally, it sounds like you may have multiple instances of this Trade value in the other sheet, which means there could be multiple "Sub" values to return. You may want to try using a JOIN(COLLECT formula in this instance instead, so that if there are more than one selections you can see them all in one cell.

Try this:

=IF(ISBLANK(Trade@row), " ", JOIN(COLLECT({Sub}, {Trade}, HAS(@cell, Trade@row)), " / "))

Let me know if this makes sense and if it works for you!

Cheers,

Genevieve

Diana Gomes

I had this exact same question and this solution just rocked my world! Thank you!

Genevieve P.

Happy to hear that, @Diana Gomes! No problem at all.