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...
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,
Sure, I probably explained it wrong. I really appreciate your assistance with this!
I want to look up the values in column A in the range of B. So, using the value in A2 as an example, 22222, I want to find it in column B. The formula should find 22222 in cell B3 and return the value in column C. Which would be C3, a value of "B".
Using the value in A3 (33333), the formula would also find a match in cell B3, and return the value of "B" from C3.
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,
- There it is!! Thank you so much Hans! 😄
I was just looking at the [match_mode] argument of xlookup the other day thinking, "I should figure out what that wildcard option is all about." Then, apparently, immediately forgot about it.. lol