Excel - multiple value search across multiple columns or one column with multiple values

Question

I have 7 criteria = TMO-1 through to TMO-7

I have two scenarios to search from.

i have either got a single excel with TMO-6, TMO-201, TMO-67,... etc (some have a lot of values)

or i have split the cell up so the values are all in individual cells such that [TMO-6][TMO-201][TMO-67] etc

I have tried two equations from each. for the first one (the preferred solution) i have tried:

=IF(IFERROR(SEARCH("TMO-1",AB8),0) > 0, "TMO-1",IF(IFERROR(SEARCH("TMO-2",AB8),0) > 0, "TMO-2", "false"))

the problem with that is it finds anything that starts with TMO-1, so will show true if TMO-12 is in the cell.

For option 2 i tried:

=IF(AB9:AR9=TMO-1, TMO-1, IF(AB9:AR9=TMO-2, TMO-2, IF(AB9:AR9=TMO-3, TMO-3,IF(AB9:AR9=TMO-4, TMO-4, IF(AB9:AR9=TMO-5, TMO-5, IF(AB9:AR9=TMO-6, TMO-6, IF(AB9:AR9=TMO-7, TMO-7, "N/A")))))))

and i get the error #spill

any ideas ?

Answer 1

Assuming:

• ms365 (Hence the #SPILL error);
• The option between concatenated values or seperated (hence AB8 against AB9:AR9);
• All numbers are prepended with TMO-;
• You are looking for the 1st match in sequence (1-7);
• If no match is found, you want to return "Not Found".

---

First thing that came to mind is to just keep the comma-seperated data in AB8 and use a simple trick to concatenate the delimiters with the sequence:

=ISNUMBER(FIND("-"&SEQUENCE(7)&",",A1&","))

To put that in practice, try:

Formula in B1:

=IFERROR(MATCH("X",IF(ISNUMBER(FIND("-"&SEQUENCE(7)&",",A1&",")),"X"),0),"Not Found")

---

Other options:

=@IFERROR(SORT(FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>")&"</s></t>","//s[substring(.,5)<8]")),"Not Found")

Or, using the insider BETA-functions:

=LET(X,MIN(--DROP(TEXTSPLIT(A1,"-",", "),,1)),IF(X<8,"TMO-"&X,"Not Found"))