VLOOKUP with Multiple Results

[Admin]

Hi All,

VLOOKUP is one of the Excel's most useful as well as commonly used functions.

Here are some examples.

Data table:

Sheet1

*	A	B	C	D
1	Order ID	Product	Unit Price	Quantity
2	10248	Queso Cabrales	14.00	12
3	10248	Singaporean Hokkien Fried Mee	9.80	10
4	10248	Mozzarella di Giovanni	34.80	5
5	10249	Tofu	18.60	9
6	10249	Manjimup Dried Apples	42.40	40
7	10250	Jack's New England Clam Chowder	7.70	10
8	10250	Manjimup Dried Apples	42.40	35
9	10250	Louisiana Fiery Hot Pepper Sauce	16.80	15
10	10251	Gustaf's Knäckebröd	16.80	6
11	10251	Ravioli Angelo	15.60	15
12	10251	Louisiana Fiery Hot Pepper Sauce	16.80	20

Excel tables to the web >> Excel Jeanie HTML 4


a simple VLOOKUP


Sheet1

*	A	B	C	D
18	Order ID	Product	Unit Price	Quantity
19	10248	Queso Cabrales	14	12

Spreadsheet Formulas

Cell Formula B19 =VLOOKUP($A19,$A$2:$D$12,2,0) C19 =VLOOKUP($A19,$A$2:$D$12,3,0) D19 =VLOOKUP($A19,$A$2:$D$12,4,0)

Excel tables to the web >> Excel Jeanie HTML 4

continued on next post..

[Admin]

Extract multiple results with the help of Helper column

Sheet1

*	E	F
1	0	3
2	1	*
3	2	*
4	3	*
5	*	*

Spreadsheet Formulas

Cell Formula F1 =MAX(E1:E12) E2 =IF(A2=$A$25,LOOKUP(9.9999E+307,$E$1:E1)+1,"") E3 =IF(A3=$A$25,LOOKUP(9.9999E+307,$E$1:E2)+1,"") E4 =IF(A4=$A$25,LOOKUP(9.9999E+307,$E$1:E3)+1,"") E5 =IF(A5=$A$25,LOOKUP(9.9999E+307,$E$1:E4)+1,"")

Excel tables to the web >> Excel Jeanie HTML 4



Sheet1

*	A	B	C	D
24	Order ID	Product	Unit Price	Quantity
25	10248	Queso Cabrales	14	12
26	*	Singaporean Hokkien Fried Mee	9.8	10
27	*	Mozzarella di Giovanni	34.8	5
28	*	*	*	*

Spreadsheet Formulas

Cell Formula B25 =IF(ROWS(B$25:B25)<=$F$1,LOOKUP(ROWS(B$25:B25),$E$2:$E$12,B$2:B$12),"") C25 =IF(ROWS(C$25:C25)<=$F$1,LOOKUP(ROWS(C$25:C25),$E$2:$E$12,C$2:C$12),"") D25 =IF(ROWS(D$25:D25)<=$F$1,LOOKUP(ROWS(D$25:D25),$E$2:$E$12,D$2:D$12),"") B26 =IF(ROWS(B$25:B26)<=$F$1,LOOKUP(ROWS(B$25:B26),$E$2:$E$12,B$2:B$12),"") C26 =IF(ROWS(C$25:C26)<=$F$1,LOOKUP(ROWS(C$25:C26),$E$2:$E$12,C$2:C$12),"") D26 =IF(ROWS(D$25:D26)<=$F$1,LOOKUP(ROWS(D$25:D26),$E$2:$E$12,D$2:D$12),"") B27 =IF(ROWS(B$25:B27)<=$F$1,LOOKUP(ROWS(B$25:B27),$E$2:$E$12,B$2:B$12),"") C27 =IF(ROWS(C$25:C27)<=$F$1,LOOKUP(ROWS(C$25:C27),$E$2:$E$12,C$2:C$12),"") D27 =IF(ROWS(D$25:D27)<=$F$1,LOOKUP(ROWS(D$25:D27),$E$2:$E$12,D$2:D$12),"") B28 =IF(ROWS(B$25:B28)<=$F$1,LOOKUP(ROWS(B$25:B28),$E$2:$E$12,B$2:B$12),"") C28 =IF(ROWS(C$25:C28)<=$F$1,LOOKUP(ROWS(C$25:C28),$E$2:$E$12,C$2:C$12),"") D28 =IF(ROWS(D$25:D28)<=$F$1,LOOKUP(ROWS(D$25:D28),$E$2:$E$12,D$2:D$12),"")

Excel tables to the web >> Excel Jeanie HTML 4

continued on next post..

[Admin]

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))),"")}

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4