Using INDEX and SMALL(IF) to return values horizontally

[mxgarrison]

Hi. I have 2 sets of data in one excel spreadsheet tab. One is unique PN's (aka Column G). Other is duplicate PN's with unique locations (aka Columns A1-C10). Below is my formula I'm working on.

=IFERROR(INDEX($A$1:$C$10,SMALL(IF($G$1=$A$1:$A$10,ROW($A$1:$A$10)-ROW($A$1)+1), COLUMN($I$1)),3),"???")

All I'm getting back in column I1 is ???. Below is my data:

A	B	C	D	E	F	G
123456-0001	Widget 1	A01A				789123-0015
123456-0001	Widget 1	A01D				456789-0002
123456-0001	Widget 1	A02C				123456-0001
123456-0001	Widget 1	A05D
123456-0001	Widget 1	B04D
123456-0001	Widget 1	B06A
456789-0002	Widget 2	B05C
456789-0002	Widget 2	B14A
789123-0015	Widget 3	A05D
789123-0015	Widget 3	A04D

I've been working on this forever and I'm just stuck. Please help.

Thanks
-Mark

 

[mxgarrison]

I did forget to mention I want my data like this:

789123-0015 A05D A04D
456789-0002 B05C B14A
123456-0001 A01A A01D A02C A05D B04D B06A

 

[AhoyNC]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	123456-0001	Widget 1	A01A				789123-0015	A05D	A04D
2	123456-0001	Widget 1	A01D				456789-0002	B05C	B14A
3	123456-0001	Widget 1	A02C				123456-0001	A01A	A01D	A02C	A05D	B04D	B06A
4	123456-0001	Widget 1	A05D
5	123456-0001	Widget 1	B04D
6	123456-0001	Widget 1	B06A
7	456789-0002	Widget 2	B05C
8	456789-0002	Widget 2	B14A
9	789123-0015	Widget 3	A05D
10	789123-0015	Widget 3	A04D
Sheet1

Cell Formulas
Range	Formula
H1:M3	H1	=IFERROR(INDEX($C$1:$C$10,AGGREGATE(15,6,(ROW($A$1:$A$10)-ROW($A$1)+1)/($A$1:$A$10=$G1),COLUMNS($H$1:H1))),"")

 

[mxgarrison]

That works! Thanks! But now I need to add in another 2 columns into the array. A qty in column D and ID in E. So each location in column C will have a qty in D and an ID in E. How do I add that in?