LOOKUP & COMBINE

[ekhawaja]

So I am looking for a formula for column"result" that looks up the value from LOOKUP in DATA1, and if the value is repeated in DATA1, the result should show both values of DATA2 "concatenated" (separated by comma ",".).....see table below:

I have data in the range of 1000s that I will be running this against. If no value is found, result should show blank. Thanks in advance!

DATA1	DATA2		LOOKUP	RESULT
54-PX-142	JAY		51-PX-370	AFZ
71-PX-614B	ALI		41-PX-156
71-PX-132B	RIA		54-PX-142	JAY,JAY,KAY
42-SV-280	MIA		30-PX-140
54-PX-142	JAY		42-SV-280	MIA
71-PX-132B	RIO
51-PX-370	AFZ
54-PX-142	KAY

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1
2	DATA1	DATA2		LOOKUP	RESULT
3	54-PX-142	JAY		51-PX-370	AFZ
4	71-PX-614B	ALI		41-PX-156
5	71-PX-132B	RIA		54-PX-142	JAY,JAY,KAY
6	42-SV-280	MIA		30-PX-140
7	54-PX-142	JAY		42-SV-280	MIA
8	71-PX-132B	RIO
9	51-PX-370	AFZ
10	54-PX-142	KAY
Sheet3

Cell Formulas
Range	Formula
E3:E7	E3	=TEXTJOIN(",",1,FILTER(B$3:B$10,A$3:A$10=D3,""))

 

[ekhawaja]

thank you so much @Eric W ..it works!