Can I index/match multiple criteria in one cell?

Josh Reed

I have a column that is a multi select dropdown of project codes.

The codes are matched with a description on another sheet. Some rows have multiple project codes in the cell that has the multi select dropdown.

Is there a way to have the description come in for each selection, not just the first?

My suggestion to the team was to use multiple columns, one for each code, then the index/match would work fine, but there would be multiple columns for the codes and the descriptions. We could then hide all the description columns and add another that concatenates them all in the right order, but there's no way around having multiple columns for each code I can think of.

Does anyone know of a solution for this?

Paul Newcome

Are you able to provide a screenshot for reference?

Zachary Hall

You could use the CONTAINS( function to check if the multi-select has the items selected.

Josh Reed

@Paul Newcome Yessir! So currently it is just matching the first selection in the multiselect cell, which makes sense. We basically are trying to run the index/match x times within the same cell which does not seem possible to me... and then spit out the x number of results into one cell.

Josh Reed

I was thinking of trying to use LEFT or something like that to run the index and match multiple times and concatenate the results all in one giant formula... but I don't think that will work. How would I select just the middle.

I really don't see a way around each code needing it's own column.

Paul Newcome

You could try a JOIN/COLLECT with a HAS for one of the criteria.

=JOIN(COLLECT({Column To Pull From}, {Code Column}, HAS(Code@row, @cell)), " ")

Kate H

@Paul Newcome In the formula you provided, is the HAS(Code@row, @cell)), " ") portion, does the "Code" refer to the entire column name? I have a column name that is two words so I put curly braces in it and it says unparseable. Any insight?

Paul Newcome

@Kate H Yes. Code@row is the column in the sheet containing the formula that houses what you are trying to match on. If your column name has at least one space, number, and / or special character, you use [Square Brackets].

If you are still having trouble, feel free to post the formula you are using that is throwing the error.

Kate H

@Paul Newcome

=JOIN(COLLECT({Group Focals Range 1}, [Impacted Teams], HAS([Impacted Teams]@row, @cell)), ";")

where I'm trying to pull in the focal email from another sheet, Impacted Teams is a column in the current sheet where this formula is

It's still stating #UNPARSEABLE

Paul Newcome

@Kate H That first [Impacted Teams] should be a {cross sheet reference} to the source sheet's column that would have the matching data.