Excel one cell multiple vlookup comma separated inputs and one cell multiple comma separeted output

0
votes

I have problem with vlookup multiple comma separated values in one cell and coma separated output in one cell.

I tried to do it by the functions and trimming text and tried to split it in other worksheets and nothing works.

Here what I want to achive

Is the VBA necessary to solve this problem?

2
excelvbavlookupcomma
Is the VBA necessary to solve this problem? that depends on whether you have Office 365 or later. – Scott Craner
Please show what you have tried, and what results you have gotten. Also, please state which version of Excel you have. – Ron Rosenfeld
I am using Excel for O365 so the latest one. I tried to adapt solutions from this page mrexcel.com/forum/excel-questions/…, but in the best way it returns only one output. Other thinks like splitting and connecting data in other spreadsheets doesn't work and didn't looked smart. – Christoph. O

2 Answers

1
votes

You can use this formula:

=TEXTJOIN(",",TRUE,INDEX($F$2:$F$4,N(IF(1,MATCH(FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s"),$E$2:$E$4,0)))))

This is an array formula. To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

enter image description here

0
votes

If you have Office 365 Excel then you can use TEXTJOIN as an array formula:

=TEXTJOIN(", ",TRUE,IFERROR(INDEX(E:E,N(IF({1},MATCH(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(ROW($XFD$1:INDEX($XFD:$XFD,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*99+1,99)),D:D,0)))),""))

Being an array formula it must be confirmed with Ctrl-Shift-enter instead of Enter when exiting edit mode.

enter image description here

If you do not have Office 365 you can use the UDF HERE and the formula above to call it.