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
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!
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.
There is a way but the formula would be heavier than this, and be less efficient.
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.
Thank You!!! I have been struggling to find a formula for weeks now for my problem, and here it is!
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.
_ 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***.
not working, returns 0 as an answer
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.
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*
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.
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!!
See if this picture helps you (edit the formula to fit your actual ranges):
..or this picture (edit the formula to fit your actual ranges):
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.
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.
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.
*index the minimum non-zero score
Just getting to this after being out last week.
What exactly do you mean by “index”?
Give a small sample of your data, your criteria, and your expected result and we can take it from there.