vlookup in a cell with comma separated values returned in another comma separated value

[jtambard]

Hello,

Is is possible to do a vlookup in a cell with comma separated values and return it in another comma separated value (in red)
example :
I want top obtain Col B result vlooking up in second table. Your help is the most welcome.
Thanks
Jacques.

	A	B
1	GERMANY,SPAIN,FRANCE	DE,SP,FR
2	FRANCE,ARGENTINA	FR,AR
3	DENMARK	DK

Vlookup table :

ARGENTINA	AR
GERMANY	DE
FRANCE	FR
SPAIN	SP
DENMARK	DK

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	GERMANY,SPAIN,FRANCE	DE, SP, FR				ARGENTINA	AR
3	FRANCE,ARGENTINA	FR, AR				GERMANY	DE
4	DENMARK	DK				FRANCE	FR
5						SPAIN	SP
6						DENMARK	DK
Main

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTJOIN(", ",,INDEX($G$2:$G$6,MATCH(FILTERXML("<k><m>"&SUBSTITUTE(A2,",","</m><m>")&"</m></k>","//m"),$F$2:$F$6,0)))

 

[jtambard]

A big thank you Fluff ! This was of great help !
???

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Sbalan]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	GERMANY,SPAIN,FRANCE	DE, SP, FR				ARGENTINA	AR
3	FRANCE,ARGENTINA	FR, AR				GERMANY	DE
4	DENMARK	DK				FRANCE	FR
5						SPAIN	SP
6						DENMARK	DK
Main

Cell Formulas
Range	Formula
B2:B4	B2	=TEXTJOIN(", ",,INDEX($G$2:$G$6,MATCH(FILTERXML("<k><m>"&SUBSTITUTE(A2,",","</m><m>")&"</m></k>","//m"),$F$2:$F$6,0)))

Thank you so much, this was very helpful Can you suggest the same formula without repetation

 

[Fluff]

Please start a thread of your own for this question & post some sample data. Thanks

 

[stevemcmillen]

Alternate Formula that works on Excel Mac:
=TEXTJOIN(", ",TRUE,XLOOKUP(TEXTSPLIT(SUBSTITUTE(C4," ",""),","),range_to_search,range_to_insert,"",0,1))

where range_to_search is a range of cells and range_to_insert is same size range with corresponding values to insert in output.

The separator in TEXTSPLIT and TEXTJOIN an be customized to suite need.