Cell Contains Comma Delimited Items. For Each Delimited Item, XLookup(?) and Concatenate the Results Formula Only (No VBA) (LAMBDA??)

[RHK123]

Hello Excel Experts!

I'm stuck.

Here is a simplified description of my problem:

1. I have a lookup table.
2. I have a cell that contains comma delimited values.
3. I need to move thru the cell with the comma delimited values, perform a lookup of each delimited value against the lookup table, and concatenate the results of the current lookup to the previous lookup results.

The attached image is a visualization of the problem.
The only formula I used here is to determine the count of delimited items in cells C11: C14
That formula in D11 =LEN(C11)-LEN(SUBSTITUTE(C11,",",""))+1

I've never actually used Lambda, but I've read that recursive Lambda can "loop". Can it solve this problem?


Many Thanks!

 

[Cubist]

Try:

Book2
	C	D	E
1		Lookup
2		1	AAA
3		2	BBB
4		3	CCC
5		4	DDD
6
7
8
9
10	Input		Output
11	3		CCC
12	2, 4, 1		BBB, DDD, AAA
13	1, 2		AAA, BBB
14	3, 4 ,1 , 2		CCC, DDD, AAA, BBB
Sheet12

Cell Formulas
Range	Formula
E11:E14	E11	=TEXTJOIN(", ",,XLOOKUP(--TEXTSPLIT(C11,",",,1),$D$2:$D$5,$E$2:$E$5))

 

[RHK123]

Amazing!
Thank you for saving me from going down the wrong path.

 

[RHK123]

Amazing!
Thank you for saving me from going down the wrong path.

 

[RHK123]

Uh Oh!
On 2nd thought guess I simplified my problem too much!
When I use letters instead of number as the lookup index the solution doesn't work.
Do you have a workaround? Sorry

 

[Cubist]

Remove the -- before TEXTSPLIT.

 

[RHK123]

Remove the -- before TEXTSPLIT.

 

[Cubist]

Make sure the look up table in D are all text including the numbers. Updated formula below.

Book2
	C	D	E
1		Lookup
2		1	AAA
3		2	BBB
4		x	CCC
5		y	DDD
6
7
8
9
10	Input		Output
11	1		AAA
12	2,1		BBB, AAA
13	x, y		CCC, DDD
14	x		CCC
Sheet12

Cell Formulas
Range	Formula
E11:E14	E11	=TEXTJOIN(", ",,XLOOKUP(TRIM(TEXTSPLIT(C11,",",,1)),$D$2:$D$5,$E$2:$E$5))