Lookup next match for multiple criteria

[Caly]

Hi I have a list of items that have different ways of referring to the item and I want to have each of those listings running across in columns instead of rows. I tried a pivot but that will only display the results in rows. So as a workaround I did a count of and then grouped the numbers rod listings. But what formula can I use to bring in multiple listings?



item. References
Apple. Pie
Apple. Shape
Banana. Pancake
Blueberry. Pie
Blueberry. Yogurt

I want it to look like this
Item. Reference 1. Reference 2
Apple. Pie. Shape
Banana. Pancake
Blueberry. Pie. Yogurt

 

[AhoyNC]

With Excel 365 you could try TRANSPOSE and FILTER functions.

Book1
	A	B	C	D
1	item	References
2	Apple	Pie
3	Apple	Shape
4	Banana	Pancake
5	Blueberry	Pie
6	Blueberry	Yogurt
7
8
9	Apple	Pie	Shape
10	Banana	Pancake
11	Blueberry	Pie	Yogurt
Sheet1

Cell Formulas
Range	Formula
B9:C9,B11:C11,B10	B9	=TRANSPOSE(FILTER($B$2:$B$6,A9=$A$2:$A$6,""))
Dynamic array formulas.

 

[Peter_SSs]

Further, you can use the UNIQUE function to get the list of fruit.

21 08 13.xlsm
	A	B	C	D	E	F
1	item	References
2	Apple	Pie		Apple	Pie	Shape
3	Apple	Shape		Banana	Pancake
4	Banana	Pancake		Blueberry	Pie	Yogurt
5	Blueberry	Pie
6	Blueberry	Yogurt
To rows

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A6)
E2:F2,E4:F4,E3	E2	=TRANSPOSE(FILTER(B$2:B$6,A$2:A$6=D2,""))
Dynamic array formulas.

 

[Caly]

Thank you - this worked perfectly.

 

[Peter_SSs]

Glad we could help. Thanks for the follow-up.