SOLVED

# Need a lookup that searches for a value in a column of cells that each contain multiple values

Copper Contributor

# 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.

6 Replies

# Re: Need a lookup that searches for a value in a column of cells that each contain multiple values

In D2:

=TEXTJOIN(", ", TRUE, FILTER(\$C\$2:\$C\$5, ISNUMBER(SEARCH(\$A\$2:\$A\$5,B2)), ""))

Fill down.

# Re: Need a lookup that searches for a value in a column of cells that each contain multiple values

@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?

# Re: Need a lookup that searches for a value in a column of cells that each contain multiple values

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?

# Re: Need a lookup that searches for a value in a column of cells that each contain multiple values

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.

best response confirmed by Big_Willy (Copper Contributor)
Solution

# Re: Need a lookup that searches for a value in a column of cells that each contain multiple values

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,

# Re: Need a lookup that searches for a value in a column of cells that each contain multiple values

There it is!! Thank you so much Hans! :D
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
1 best response

Accepted Solutions
best response confirmed by Big_Willy (Copper Contributor)
Solution

# Re: Need a lookup that searches for a value in a column of cells that each contain multiple values

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,