Vlookup help for comma separated values

[rik81h]

Hi all,

New member here, relative novice in terms of excel usage.
I have a workbook i'm trying to put together with monthly worksheets containing date ranges. I have an extra worksheet with some data in, just 2 columns which contain a "job code" A-G with a description for each in the next column.

I want to be able to enter a code in the worksheets and it autofil the next column with the description. A friend helped me with this giving me a vlookup formula =IFERROR(VLOOKUP(B2,Descriptions!$A$2:$C$10,2,FALSE),"") however this does not work with multiple comma separated code values. I need to be able to enter A, C for example or maybe even 3 or more in to one cell.

Any help would be appreciated, TIA.

 

[KOKOSEK]

Do you want to enter into B2 more than 1 value like A, C and expect result like: Description 1, Description 2 etc.
Am I correct?

 

[rik81h]

Do you want to enter into B2 more than 1 value like A, C and expect result like: Description 1, Description 2 etc.
Am I correct?

Yes kokosek that is spot on

 

[KOKOSEK]

Try like this:

Book1
	B	C	D	E	F
2	A	Description A		A, C, E	Description A, Description C, Description E
3	B	Description B		B, E	Description B, Description E
4	C	Description C
5	D	Description D
6	E	Description E
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=TEXTJOIN(", ",1,VLOOKUP(TRANSPOSE(FILTERXML("<t><s>" & SUBSTITUTE(E2,",","</s><s>") & "</s></t>", "//s")),$B:$C,2,0))

as I read TRANSPOSE works in Excel 2019 as array formula, so you have to accept it with Ctrl+Shift+Enter.

 

[rik81h]

Try like this:

Book1
	B	C	D	E	F
2	A	Description A		A, C, E	Description A, Description C, Description E
3	B	Description B		B, E	Description B, Description E
4	C	Description C
5	D	Description D
6	E	Description E
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=TEXTJOIN(", ",1,VLOOKUP(TRANSPOSE(FILTERXML("<t><s>" & SUBSTITUTE(E2,",","</s><s>") & "</s></t>", "//s")),$B:$C,2,0))

as I read TRANSPOSE works in Excel 2019 as array formula, so you have to accept it with Ctrl+Shift+Enter.

Thank you kokosek so that works as i need in your example but when i plug in my source it only shows the 1 returned answer using =TEXTJOIN(", ",1,VLOOKUP(TRANSPOSE(FILTERXML("<t><s>" & SUBSTITUTE(B29,",","</s><s>") & "</s></t>", "//s")),Descriptions!$A$2:$B$8,2,0))

 

[rik81h]

and i've no idea regading that ctrl+shift+enter

 

[KOKOSEK]

place formula into cell but do not press Enter but accept it with Ctrl+Shift+Enter (Microsoft how to create array formula)
If You do it properly, you will see { } around formula in formula bar.

 

[rik81h]

Ok so no problem with accepting it and i have those added braces but the forumla still only returns 1 result even when i do not adjust the table array.

 

[rik81h]

I've just re-created your example exactly in a new workbook and I get the same results as in my own workbook, it is only returning the first entry. Perhaps some limitation or functionality change within 2019? And yes that is with ctrl shift entr to accept as an array. :/

 

[Peter_SSs]

I don't have your version to test but what happens if you use this formula, entered normally?

rik81h.xlsm
	A	B
1
2	A	Description A
3	B	Description B
4	C	Description C
5	D	Description D
6	E	Description E
7
8
Descriptions

rik81h.xlsm
	B	C
29	A, C, E	Description A, Description C, Description E
30	B, E	Description B, Description E
31	D	Description D
Sheet1

Cell Formulas
Range	Formula
C29:C31	C29	=TEXTJOIN(", ",1,VLOOKUP(FILTERXML("<t><s>" & SUBSTITUTE(B29,",","</s><s>") & "</s></t>", "//s"),Descriptions!A$2:B$8,2,0))