VLOOKUP with Multiple Results

**Admin** · 05-26-2011, 10:29 PM

Extract multiple results without helper column

Sheet1

*	A	B	C	D
33	Order ID	Product	Unit Price	Quantity
34	10248	Queso Cabrales	14	12
35	*	Singaporean Hokkien Fried Mee	9.8	10
36	*	Mozzarella di Giovanni	34.8	5
37	*	*	*	*

Spreadsheet Formulas

Cell	Formula
B34	{=IF(ROWS(B$34:B34)<=COUNTIF($A$2:$A$12,$A$34),INDEX(B$2:B$12,SMALL(IF($A$2:$A$12=$A$34,ROW(B$2:B$12)-ROW(B$2)+1),ROWS(B$34:B34))),"")}
C34	{=IF(ROWS(C$34:C34)<=COUNTIF($A$2:$A$12,$A$34),INDEX(C$2:C$12,SMALL(IF($A$2:$A$12=$A$34,ROW(C$2:C$12)-ROW(C$2)+1),ROWS(C$34:C34))),"")}
D34	{=IF(ROWS(D$34:D34)<=COUNTIF($A$2:$A$12,$A$34),INDEX(D$2:D$12,SMALL(IF($A$2:$A$12=$A$34,ROW(D$2:D$12)-ROW(D$2)+1),ROWS(D$34:D34))),"")}
B35	{=IF(ROWS(B$34:B35)<=COUNTIF($A$2:$A$12,$A$34),INDEX(B$2:B$12,SMALL(IF($A$2:$A$12=$A$34,ROW(B$2:B$12)-ROW(B$2)+1),ROWS(B$34:B35))),"")}
C35	{=IF(ROWS(C$34:C35)<=COUNTIF($A$2:$A$12,$A$34),INDEX(C$2:C$12,SMALL(IF($A$2:$A$12=$A$34,ROW(C$2:C$12)-ROW(C$2)+1),ROWS(C$34:C35))),"")}
D35	{=IF(ROWS(D$34:D35)<=COUNTIF($A$2:$A$12,$A$34),INDEX(D$2:D$12,SMALL(IF($A$2:$A$12=$A$34,ROW(D$2:D$12)-ROW(D$2)+1),ROWS(D$34:D35))),"")}
B36	{=IF(ROWS(B$34:B36)<=COUNTIF($A$2:$A$12,$A$34),INDEX(B$2:B$12,SMALL(IF($A$2:$A$12=$A$34,ROW(B$2:B$12)-ROW(B$2)+1),ROWS(B$34:B36))),"")}
C36	{=IF(ROWS(C$34:C36)<=COUNTIF($A$2:$A$12,$A$34),INDEX(C$2:C$12,SMALL(IF($A$2:$A$12=$A$34,ROW(C$2:C$12)-ROW(C$2)+1),ROWS(C$34:C36))),"")}
D36	{=IF(ROWS(D$34:D36)<=COUNTIF($A$2:$A$12,$A$34),INDEX(D$2:D$12,SMALL(IF($A$2:$A$12=$A$34,ROW(D$2:D$12)-ROW(D$2)+1),ROWS(D$34:D36))),"")}
B37	{=IF(ROWS(B$34:B37)<=COUNTIF($A$2:$A$12,$A$34),INDEX(B$2:B$12,SMALL(IF($A$2:$A$12=$A$34,ROW(B$2:B$12)-ROW(B$2)+1),ROWS(B$34:B37))),"")}
C37	{=IF(ROWS(C$34:C37)<=COUNTIF($A$2:$A$12,$A$34),INDEX(C$2:C$12,SMALL(IF($A$2:$A$12=$A$34,ROW(C$2:C$12)-ROW(C$2)+1),ROWS(C$34:C37))),"")}
D37	{=IF(ROWS(D$34:D37)<=COUNTIF($A$2:$A$12,$A$34),INDEX(D$2:D$12,SMALL(IF($A$2:$A$12=$A$34,ROW(D$2:D$12)-ROW(D$2)+1),ROWS(D$34:D37))),"")}