Check if a cell value is found in a column where cells may have multiple values then return a third cell value

Question

I am trying to check whether the value in each cell within Column A is in Column D. Some cells in Column D have multiple values within a single cell. If the value of Column A IS found in Column D then I want the formula to return what's in Column B (value next to Column A that matched).

Answer 1

If you are looking at just row by row analysis then try this:

=if(iserror(search(a2,d2)),"",b2)

if you are trying to match any row in D then I suggest breaking out all items in D into an item per row and then doing a VLOOKUP

Answer 2

Can you copy column B to Column E? If so,

=Vlookup("" & A1 & "", D:E, 2,0) and scroll down.

If not, a similar index(match( function will work with a wild card. (*)

Answer 3

Try:

Formula in C2:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A2,$D$2:$D$5))))>0,B2,"")

Drag down..