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.

 

[rik81h]

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))

Hi Peter, tried your solution and in a new workbook and again it is only returning the first entry. Does this seem like a problem with my version or setup? Thank you.

 

[rik81h]

@KOKOSEK @Peter_SSs ok so it was my installation, after some updates and some faffing both of your formulas are working as i need them to. Thank you both very much for your time and effort.

 

[rik81h]

One follow up question, how do i make either of those formulas return a blank instead of #VALUE! for cells not populated? i have tried adding IF((ISERROR & IFERROR but with no luck
Nevermind resolved myself with IF(B6="","",{YOUR FORMULA}

Thanks

 

[Peter_SSs]

Glad you got it resolved. Thanks for letting us know.