Tom’s Tutorials For Excel: LOOKUP By MIN or MAX

Tom’s Tutorials For Excel: LOOKUP By MIN or MAX

You can do a lookup based on other worksheet functions such as MIN and MAX.

In Picture #1, the formula =INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)) returns Isabella because her Sales Dollars number of $5,012 is the lowest (minimum) number in column B.

Picture #1


In Picture #2, the formula =INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)) returns Francesca because her Sales Dollars number of $8,289 is the highest (maximum) number in column B.

Picture #2

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
19 comments on “Tom’s Tutorials For Excel: LOOKUP By MIN or MAX
  1. Brandi says:

    I have been searching for this for the past TWO days and no one in my class could figure this out! When I input this formula and it gave me the correct answer, I seriously wanted to do the happy dance! Thank you so much for this!

  2. Monique says:

    THANK YOU so much! Is there a way to do this with vlookup? I tried http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-using-vlookup-with-min-max-and-average/ but it doesn’t work.

  3. Sankar says:

    I want to do this one step further. What would we do if the name Isabella comes twice in the list? I am thinking something like looking up dates of batches of production of a material and pulling out the oldest material.

  4. meg says:

    Thank You!!! I have been struggling to find a formula for weeks now for my problem, and here it is!

  5. Amogh says:

    Hi Tom ,

    Would you be able to advise how to work the below :

    I want to lookup the minimum value from column G where column A is duplicated and G has various values .

    Thanks for taking the time on this.

    • Tom Urtis says:

      _ ColA BCDEF ColG HI ColJ ColK
      1 Bill _____ 2679 __ Lisa 2814
      2 Lisa _____ 6233
      3 Jane _____ 1234
      4 Lisa _____ 2814
      5 Bill _____ 3118
      6 Bill _____ 1997
      7 Lisa _____ 8647
      8 Jane _____ 1526
      9 Lisa _____ 5304

      Formula in cell K1 is
      =MIN(IF(A1:A9=J1,G1:G9))
      THIS IS AN ARRAY FORMULA.
      You apply it to a cell by pressing CTRL+SHIFT+ENTER, ***not just with ENTER***.

  6. Prateek says:

    not working, returns 0 as an answer

    • Tom Urtis says:

      I just re-created this example on 2016 and it works perfectly. You must have a wrong range reference or you did not enter the formula in your spreadsheet the way I showed it, or, there is a difference in international settings between yours and mine. In any case, the formulas work, and something on your end is happening to cause the zero to be returned.

      • Paul says:

        Tom,

        The formula also returns 0 if there are matching values. Referenced in below (tried adding screen shot and wouldn’t work):

        J20 Returns value 0 as a result of F23 & F26 containing the same Price.
        Are you aware of a possible solution in order to return both results (i.e. CONCAT the values with a comma between)?

        D18 PRODUCT
        D19:D22 “Apple”
        D23:D26 “Banana”
        D27:D30 “Lemon”
        D31:D34 “Orange”
        D35:D38 “Apple Pie”
        D39:D42 “Banana Pie”
        D43:D46 “Orange Juice”

        E18 SELLER
        E19 “Comp_A”
        E20 “Comp_B”
        E21 “Comp_C”
        *Repeats, ending at E46*
        E43 “Comp_A”
        E44 “Comp_B”
        E45 “Comp_C”
        E46 “OurPrice”

        F18 PRICE
        F19 5.75
        F20 7.96
        F21 6.65
        F22 5.25
        F23 0.16
        F24 0.36
        F25 0.75
        F26 0.16
        F27 4.16
        F28 3.99
        F29 3.99
        F30 3.30
        F31 3.94
        F32 3.99
        F33 2.99
        F34 3.96
        F35 5.94
        F36 6.99
        F37 5.99
        F38 5.67
        F39 Not Offered
        F40 Not Offered
        F41 5.99
        F42 5.94
        F43 3.68
        F44 3.68
        F45 3.49
        F46 1.98

        H18 Product
        H19 =INDEX($D$19:$D$46,MATCH(0,COUNTIF($H$18:H18,$D$19:$D$46),0))
        *Array formula copied down to H25*

        I18 MIN Price
        I19 =MIN(IF($D$19:$D$46=H19,$F$19:$F$46))
        *Array formula copied down to I25*

        J18 Seller
        J19 =INDIRECT(ADDRESS(SUMPRODUCT(($D$19:$D$46=$H19)*($F$19:$F$46=$I19)*ROW($D$19:$F$46)),5))
        *Array formula copied down to J25*

        • Tom Urtis says:

          There are large and clunky formulas that can do this but their practicality in my opinion is not worth their value, especially when the count of a given item’s repeatedness is unknown. I would go for a UDF that is easier to manage and is more flexible to accommodate any number of unknown repeats of a given item.

  7. D says:

    Hi Tom,

    What would I do if I wanted to perform a vlookup but return the highest value for that person? Names in column A, Numbers in column B.
    Amy 1, Amy 4, Amy 6, Bob 3, Bob 9, Bob 2 etc.
    I want to lookup Amy’s highest value for example. What would my function look like?
    Any help would be appreciated!!

    • Tom Urtis says:

      See if this picture helps you (edit the formula to fit your actual ranges):

      • Tom Urtis says:

        ..or this picture (edit the formula to fit your actual ranges):

      • Dixon C says:

        Hi Tom,

        What is the formula from F4:F6 ? I cannot seem to find how to make the table show the adicacent cell that represemts the location of the result represented by H4-H6.

        • Tom Urtis says:

          I assume your question relates to the picture with Store #1 in cell F4, Store #2 in cell F5, and Store #3 in cell F6. That is the only picture with data in range F4:F6 that you asked about.

          There are no formulas in those cells; those labels are manually entered.

  8. G says:

    Is there a way to index for non-zero. Anyone with a zero would be assumed to be a non-participant, so I just want index those that have a score above zero.

Leave a Reply

Your email address will not be published. Required fields are marked *

*