CODEWITHSUNDEEP
excelvbaexcel-formulaoffice365
user8376557
user8376557

Reputation:

Vlookup multiple values from a cell in excel

I have two tables


Table A
ID      
----------
foo
baz, bar
foo,abc,bar
ham
egg

Table B
ID
---------
foo
baz
bar
abc
ham
burger
abc

I want to create column B in Table A. It must look up the values in Column A in Table B's Column A. With multiple values like baz, bar, both the values must be looked up, and if found the results must be stored. How should go for this?

=TEXTJOIN(",",TRUE,VLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(B2,",","</s><s>")&"</s></t>","//s"),H:H,1,FALSE)).

It outputs

foo
baz
foo
ham
#N/A

Desired Output:

foo 
baz, bar 
foo,abc,bar 
ham 
#N/A

Upvotes: 0

Views: 262

Answers (1)

JvdV
JvdV

Reputation: 75990

Try the following:

enter image description here

Formula in C1:

=TEXTJOIN(",",,XLOOKUP(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")),B$1:B$7,B$1:B$7,,0))

If you don't want #N/A then use:

=TEXTJOIN(",",,XLOOKUP(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s")),B$1:B$7,B$1:B$7,"",0))

Upvotes: 2

Related Questions