Results 1 to 3 of 3

Thread: VLOOKUP with Multiple Results

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10

    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

    *ABCD
    1Order IDProductUnit PriceQuantity
    210248Queso Cabrales14.0012
    310248Singaporean Hokkien Fried Mee9.8010
    410248Mozzarella di Giovanni34.805
    510249Tofu18.609
    610249Manjimup Dried Apples42.4040
    710250Jack's New England Clam Chowder7.7010
    810250Manjimup Dried Apples42.4035
    910250Louisiana Fiery Hot Pepper Sauce16.8015
    1010251Gustaf's Knäckebröd16.806
    1110251Ravioli Angelo15.6015
    1210251Louisiana Fiery Hot Pepper Sauce16.8020


    Excel tables to the web >> Excel Jeanie HTML 4


    a simple VLOOKUP


    Sheet1

    *ABCD
    18Order IDProductUnit PriceQuantity
    1910248Queso Cabrales1412

    Spreadsheet Formulas
    CellFormula
    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..

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Extract multiple results with the help of Helper column

    Sheet1

    *EF
    103
    21*
    32*
    43*
    5**

    Spreadsheet Formulas
    CellFormula
    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

    *ABCD
    24Order IDProductUnit PriceQuantity
    2510248Queso Cabrales1412
    26*Singaporean Hokkien Fried Mee9.810
    27*Mozzarella di Giovanni34.85
    28****

    Spreadsheet Formulas
    CellFormula
    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..

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Extract multiple results without helper column

    Sheet1

    *ABCD
    33Order IDProductUnit PriceQuantity
    3410248Queso Cabrales1412
    35*Singaporean Hokkien Fried Mee9.810
    36*Mozzarella di Giovanni34.85
    37****

    Spreadsheet Formulas
    CellFormula
    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

Similar Threads

  1. Vlookup to Return Multiple Values
    By Admin in forum Download Center
    Replies: 9
    Last Post: 02-17-2017, 07:03 PM
  2. How do I get Vlookup to return multiple matches?
    By HANOOF in forum Excel Help
    Replies: 1
    Last Post: 06-04-2013, 10:06 PM
  3. VLOOKUP produces undesired results
    By Dimitrios Charalampidis in forum Excel Help
    Replies: 1
    Last Post: 02-15-2013, 11:32 PM
  4. Vlookup - Multiple Sheets
    By Suhail in forum Excel Help
    Replies: 3
    Last Post: 01-30-2013, 06:47 PM
  5. Vlookup with multiple criteria
    By patsir in forum Excel Help
    Replies: 7
    Last Post: 08-27-2012, 11:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •