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

[Big_Willy]

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!

[#2]

@Big_Willy 

In D2:

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

Fill down.

[#3]

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

[#4]

@Big_Willy 

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?

[#5]

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.

[#6 · Accepted solution]

@Big_Willy 

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,

[#7]

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