Forum Discussion
Solved
Need a lookup that searches for a value in a column of cells that each contain multiple values
Using the above example data, I am looking for a formula that will search all of column B for a value in column A. In the case of "22222", the lookup should return "B". In the case of "33333", the lookup should return "B". In the case of "66666", it should return A. In the case of "88888", it should return D.
I've messed with a bunch of options combining Index/Match and X/VLookups with the Search function and haven't had any luck.
Please help! Thanks in advance!
Ah - I did misinterpret your question.
In D2:
=XLOOKUP("*"&A2&"*",$B$2:$B$5,$C$2:$C$5,"",2)
or
=IFERROR(VLOOKUP("*"&A2&"*",$B$2:$C$5,2,FALSE),"")
Fill down,
In D2:
=TEXTJOIN(", ", TRUE, FILTER($C$2:$C$5, ISNUMBER(SEARCH($A$2:$A$5,B2)), ""))
Fill down.
HansVogelaar Thanks Hans! I think I've keyed in the formula as you have written it. It's doing something, which is more than I've been able to accomplish, but not quite right. Did I miss something?
Perhaps I don;t understand what you want. My interpretation was:
For example, B2 contains 66666, so - according to your first post - the value to return is C.
B3 contains both 22222 and 33333, corresponding to A and B.
Can you explain what you want instead?
