VLOOKUP with Multiple Results

Printable View

05-26-2011, 10:10 PM
Admin

VLOOKUP with Multiple Results

Hi All,

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

Here are some examples.

Data table:

Sheet1 <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:216px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Order ID</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Product</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Unit Price</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Quantity</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10248</td><td style="font-family:Arial; font-size:10pt; ">Queso Cabrales</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">14.00</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10248</td><td style="font-family:Arial; font-size:10pt; ">Singaporean Hokkien Fried Mee</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">9.80</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10248</td><td style="font-family:Arial; font-size:10pt; ">Mozzarella di Giovanni</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">34.80</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10249</td><td style="font-family:Arial; font-size:10pt; ">Tofu</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">18.60</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">9</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10249</td><td style="font-family:Arial; font-size:10pt; ">Manjimup Dried Apples</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">42.40</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">40</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10250</td><td style="font-family:Arial; font-size:10pt; ">Jack's New England Clam Chowder</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">7.70</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10250</td><td style="font-family:Arial; font-size:10pt; ">Manjimup Dried Apples</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">42.40</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">35</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10250</td><td style="font-family:Arial; font-size:10pt; ">Louisiana Fiery Hot Pepper Sauce</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">16.80</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">15</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10251</td><td style="font-family:Arial; font-size:10pt; ">Gustaf's Knäckebröd</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">16.80</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">6</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10251</td><td style="font-family:Arial; font-size:10pt; ">Ravioli Angelo</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">15.60</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">15</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10251</td><td style="font-family:Arial; font-size:10pt; ">Louisiana Fiery Hot Pepper Sauce</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">16.80</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">20</td></tr></table> Excel tables to the web >> <a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

a simple VLOOKUP

Sheet1 <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:216px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Order ID</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Product</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Unit Price</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Quantity</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10248</td><td style="font-family:Arial; font-size:10pt; ">Queso Cabrales</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">14</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">12</td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B19</td><td >=VLOOKUP($A19,$A$2:$D$12,2,0)</td></tr><tr><td >C19</td><td >=VLOOKUP($A19,$A$2:$D$12,3,0)</td></tr><tr><td >D19</td><td >=VLOOKUP($A19,$A$2:$D$12,4,0)</td></tr></table></td></tr></table> Excel tables to the web >> <a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

continued on next post..
05-26-2011, 10:15 PM
Admin

Extract multiple results with the help of Helper column

Sheet1 <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">0</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F1</td><td >=MAX(E1:E12)</td></tr><tr><td >E2</td><td >=IF(A2=$A$25,LOOKUP(9.9999E+307,$E$1:E1)+1,"")</td></tr><tr><td >E3</td><td >=IF(A3=$A$25,LOOKUP(9.9999E+307,$E$1:E2)+1,"")</td></tr><tr><td >E4</td><td >=IF(A4=$A$25,LOOKUP(9.9999E+307,$E$1:E3)+1,"")</td></tr><tr><td >E5</td><td >=IF(A5=$A$25,LOOKUP(9.9999E+307,$E$1:E4)+1,"")</td></tr></table></td></tr></table> Excel tables to the web >> <a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

Sheet1 <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:216px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Order ID</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Product</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Unit Price</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Quantity</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10248</td><td >Queso Cabrales</td><td style="text-align:right; ">14</td><td style="text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td >Singaporean Hokkien Fried Mee</td><td style="text-align:right; ">9.8</td><td style="text-align:right; ">10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td >Mozzarella di Giovanni</td><td style="text-align:right; ">34.8</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td > </td><td > </td><td > </td><td > </td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B25</td><td >=IF(ROWS(B$25:B25)<=$F$1,LOOKUP(ROWS(B$25:B25),$E$2:$E$12,B$2:B$12),"")</td></tr><tr><td >C25</td><td >=IF(ROWS(C$25:C25)<=$F$1,LOOKUP(ROWS(C$25:C25),$E$2:$E$12,C$2:C$12),"")</td></tr><tr><td >D25</td><td >=IF(ROWS(D$25:D25)<=$F$1,LOOKUP(ROWS(D$25:D25),$E$2:$E$12,D$2:D$12),"")</td></tr><tr><td >B26</td><td >=IF(ROWS(B$25:B26)<=$F$1,LOOKUP(ROWS(B$25:B26),$E$2:$E$12,B$2:B$12),"")</td></tr><tr><td >C26</td><td >=IF(ROWS(C$25:C26)<=$F$1,LOOKUP(ROWS(C$25:C26),$E$2:$E$12,C$2:C$12),"")</td></tr><tr><td >D26</td><td >=IF(ROWS(D$25:D26)<=$F$1,LOOKUP(ROWS(D$25:D26),$E$2:$E$12,D$2:D$12),"")</td></tr><tr><td >B27</td><td >=IF(ROWS(B$25:B27)<=$F$1,LOOKUP(ROWS(B$25:B27),$E$2:$E$12,B$2:B$12),"")</td></tr><tr><td >C27</td><td >=IF(ROWS(C$25:C27)<=$F$1,LOOKUP(ROWS(C$25:C27),$E$2:$E$12,C$2:C$12),"")</td></tr><tr><td >D27</td><td >=IF(ROWS(D$25:D27)<=$F$1,LOOKUP(ROWS(D$25:D27),$E$2:$E$12,D$2:D$12),"")</td></tr><tr><td >B28</td><td >=IF(ROWS(B$25:B28)<=$F$1,LOOKUP(ROWS(B$25:B28),$E$2:$E$12,B$2:B$12),"")</td></tr><tr><td >C28</td><td >=IF(ROWS(C$25:C28)<=$F$1,LOOKUP(ROWS(C$25:C28),$E$2:$E$12,C$2:C$12),"")</td></tr><tr><td >D28</td><td >=IF(ROWS(D$25:D28)<=$F$1,LOOKUP(ROWS(D$25:D28),$E$2:$E$12,D$2:D$12),"")</td></tr></table></td></tr></table> Excel tables to the web >> <a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

continued on next post..
05-26-2011, 10:29 PM
Admin

Extract multiple results without helper column

Sheet1 <table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:216px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Order ID</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Product</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Unit Price</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Quantity</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="font-family:Arial; font-size:10pt; text-align:right; ">10248</td><td >Queso Cabrales</td><td style="text-align:right; ">14</td><td style="text-align:right; ">12</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td > </td><td >Singaporean Hokkien Fried Mee</td><td style="text-align:right; ">9.8</td><td style="text-align:right; ">10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td > </td><td >Mozzarella di Giovanni</td><td style="text-align:right; ">34.8</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td > </td><td > </td><td > </td><td > </td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B34</td><td >{=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))),"")}</td></tr><tr><td >C34</td><td >{=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))),"")}</td></tr><tr><td >D34</td><td >{=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))),"")}</td></tr><tr><td >B35</td><td >{=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))),"")}</td></tr><tr><td >C35</td><td >{=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))),"")}</td></tr><tr><td >D35</td><td >{=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))),"")}</td></tr><tr><td >B36</td><td >{=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))),"")}</td></tr><tr><td >C36</td><td >{=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))),"")}</td></tr><tr><td >D36</td><td >{=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))),"")}</td></tr><tr><td >B37</td><td >{=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))),"")}</td></tr><tr><td >C37</td><td >{=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))),"")}</td></tr><tr><td >D37</td><td >{=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))),"")}</td></tr></table></td></tr><tr><td >Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!</td></tr></table> Excel tables to the web >> <a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>