Array formula question

[Charlie1957]

Good afternoon:
I think I can’t use a vlookup since that only returns the first value, right? For many of the Purchase order in the 2nd file – there are multiple commodities. I did some research – would an array formula work maybe?
<o> </o>
My goal is to add a commodity description to the 2nd file below for each commodity in a purchase order.
<o> </o>
So anytime the PO # matches between the 2 files I want all the cmdty descriptions in the first file to be copied to the cmdty description column in the 2nd file. If I do a vlookup, I will get only the first value which is no good if there are multiple commodities per purchase order.

1st file:
PO # Cmdty Desc
100 ABC
101 DEF
101 FFF
101 GGG
102 XXX

2nd File

PO# Cmdty Descr.
100 wil have 1 descr. copied
101 will have 3 descr, copied for 1st file
102 will have 1 descr, copied.

Thanks so much!

 

[Eric W]

Here are a couple of ways:

Book1
	A	B	C	D	E	F	G	H
1	PO #	Cmdty Desc			PO#	Cmdty Desc
2	100	ABC			101	DEF, FFF, GGG
3	101	DEF			101	DEF	FFF	GGG
4	101	FFF
5	101	GGG
6	102	XXX
Sheet3

Cell Formulas
Range	Formula
F2	{=TEXTJOIN(", ",TRUE,IF(A2:A6=E2,B2:B6,""))}
F3	{=IFERROR(INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$E3,ROW($A$2:$A$6)-ROW($A$2)+1),COLUMNS($F3:F3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

If you have Excel 2016 with the new TEXTJOIN function, you can use the formula in F2. Since it's an array formula, confirm with Control+Shift+Enter.

If you have an older version of Excel, you can use the formula in F3, confirm with CSE, and copy it to the right as far as needed. If you want the different values concatenated into a single cell like in F2, we'd need to add a helper row.

Hope this helps!