Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE

Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE

You can nest a function as the lookup_value argument with VLOOKUP, to return an item relating to the lookup_value function. In the pictured example, MIN, MAX, and AVERAGE are nested to return the name of the salesperson associated with those functions.

The formula in cell D2 is =VLOOKUP(MIN(A4:A22),A4:B22,2,0)
The formula in cell E2 is =VLOOKUP(MAX(A4:A22),A4:B22,2,0)
The formula in cell F2 is =VLOOKUP(AVERAGE(A4:A22),A4:B22,2,1)

The VLOOKUP function’s fourth argument is range_lookup. For the MIN and MAX functions, the range_lookup is 0, which could have also been written as FALSE or simply omitted altogether. This deals with your desire and expectation that an exact lookup value will be found. In this example, you know that a specific minimum and maximum number will be found in the list in column A.

However, you cannot be assured that the average of those numbers will specifically exist in the list. The average of the numbers in column A is 5218, not found in column A, so, the 1 (which could have been written as TRUE) was necessary in order for the formula to look up an approximate match.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,
58 comments on “Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE
  1. Kent W. Vosburg says:

    I am attempting to use a vlookup with a MIN, but I want it to display a time based off a given value.

    What I have is 4 race lengths, 3.1, 6.2, 13.1, 26.2 miles.

    For example, B26 will be the value 6.2(length of a race)
    I want to display the best time (MIN) given for all resutls that are 6.2 in length.

    I would then repeat this for the other race lengths.

    =VLOOKUP(MIN(C2:C21)=B26,C2:J21,2,0)

    Can you please help me fingure out where I went wrong? Column C is Race length, Column J is the Times.

    Thanks
    Kent

    • Tom Urtis says:

      Hello Kent, thanks for following my Excel pages. An array formula would definitely do it, for example:

      =MIN(IF(C2:C21=B26,J2:J21))

      Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

  2. Mohammed Shoeb Ali says:

    this is not working for me as I am getting a Error as #NA. I have the followed the step by Step but not able to get the Desired Result

    • Tom Urtis says:

      Thanks for following my Excel blog.

      I know the formulas work because I tested them all before posting this topic. Take a close look at your worksheet and where the column ranges are that contain the numbers you are summing or averaging. This really does work. If you keep having problems, send me a sample workbook with what you have tried that is not working so I can see what you are working with.

      • Lorjhun says:

        This is not working for me also 🙁

        • Tom Urtis says:

          “not working” tells me nothing. Error? What kind? Wrong number or value returned? What kind? Something else happens? What?

          The formula does work, with a picture to show how it works. Check your range references and exactly how you entered the formula. If you are stil stuck, instead of just saying something doesn’t work, explain your data layout, what your expected results are, and what formula you entered that attempted to get those results.

        • Ute-S says:

          To all who say “Does not work”: Which language version of Excel do you use? As I am using a non-English version (in my case German) myself, my experience is that one of the most common errors is that by re-creating the sample, the wrong decimal points and deliminators in formulas have been used. Please make sure that those have been adapted to your regional settings when copying the formula and sample. After correcting that, the formulas for MIN and MAX work for me as expected.
          For the AVERAGE to work correctly, the list should be sorted by sales value, as VLOOKUP with range_value 1 needs a sorted list to find the approximate match.

    • Desire says:

      Column with values should come first(ie: Column A) and names after(ie: Column B)

  3. Mindy says:

    Help. I have a very similar situation. 2 columns A10:A18 are text fields (the name of a company) B10:B18 are the corresponding values. I want a cell at the bottom that looks at the values from B10:B18 finds the minimum and returns the corresponding company name from A10:A18. I am using your formula posted above and receiving the N/A error. I modified it to this:

    =VLOOKUP(MIN(B10:B18),A10:B18,1,FALSE)

  4. Desire says:

    You can even Look up the 2nd , 3rd,… best, or worst salesperson using :
    =VLOOKUP(LARGE(A4:A22,2),A4:B22,2,0)
    =VLOOKUP(SMALL(A4:A22,3),A4:B22,2,0)

    • Ollie says:

      Hi Desire

      This is a great formula and I’ve implemented it nicely. One question I have is how you would go about dealing with non-unique look up values (in your case the sales figure). If after the highest seller, the next two sales figures were the same, the second and third values being looked up against back would bring you back the name further up the list for both the two different formulas, rather than both.

      If you’ve got any ideas on this I’d really like to hear them!

      Many thanks

      Ollie

  5. Jess says:

    Hi Tom,

    I tried your steps and mine didnt work as well. is it possible for me to send u my spreadsheet for u to find out the error?

    Cheers,
    Xin Ying

    • Tom Urtis says:

      Can you please explain what “didn’t work” means? I know the formulas do work because I took the screen shots with them in action. Please describe your worksheet and what your expected results are.

  6. Stuart says:

    Hello. Can you help me?
    I want to take this one step futher, and do a VLOOKUP on a MAX IF function. Imagine in your example above you expanded column C to be Nationality, and so you wanted to return the person who is the highest salesperson from each Nationality. I would imagine a VLOOKUP MAXIF would work in principle but I cant seem to do it.

  7. Mark B. says:

    Is it possible to do this vlookup function across multiple worksheets (tabs)? I am struggling to get the table_array part. Thanks!

    • Mike D says:

      1) Why is your data on multiple tabs?
      Select Case ‘DoWeNeedMultipleTabs’
      Case 1 (Yes)
      1. You can use another sheet and INDIRECT() to aggregate the data from all tabs, then use your vlookup to find the *one* MIN, MAX and AVERAGE from this table. You will only get a single value for each.
      2. If you want the MIN, MAX and AVERAGE for each tab, then create a grid with MIN MAX and AVERAGE along one axis (let’s say as headers, like in Tom’s example above) then use the Tab names as row headers along the other axis – you can then use INDIRECT() to pull the three values from each individual tab. The bonus is you *could* use the same formula for the entire range.
      Case 2 (No)
      1. See case 1 option 1.

  8. Kamran Mumtaz Ahmed says:

    Good job Tom Urtis… 🙂
    Vlookup only works from left to right…

  9. Marc says:

    In Excel 2000 I’m trying to use a spreadsheet to track about 50 commodity purchases. I’d like to see the high, low and most recent purchase price for each. All the data is captured in a single spreadsheet (1A:192R) sorted in ascending order by the commodity (1A:192A). I tried the MIN/MAX IF with a VLOOKUP and it’s only returning the value of the first occurrence of the commodity in the list. How can I get it to search multiple rows of data that meet the search criteria and to return the needed value? I’ve input the following formula as both a normal and array formula and the result is the same. Obviously, I’m missing something…

    =MIN(IF(VLOOKUP(A2,Raw_Data,17,FALSE), VLOOKUP(A2,Raw_Data,17,FALSE), “”))

  10. Branden says:

    I tried to use this too and it didn’t work. I realized after about 20 minutes of trouble shooting that my problem was that the numerical value was on the right instead of the left. If this is the case, it will not work. Once I moved the columns so that the numerical value was in the first column and the alpha values were in the second, it worked as described.

  11. pradeep says:

    Hi

    i want to take max value thr lookup if data is diasding order.

  12. Elaine says:

    Tom
    Love this and everything else about Excel. I often just do the “drill” so that I am familiar. The MIN and MAX work as it should however the AVERAGE is not. I tried the formula using =VLOOKUP(AVERAGE(A4:A22),A4:B22,2,1) and also tried TRUE.
    I get the same answer with both however the answer is not correct. Any ideas?

    $3,375.00 Ari
    $31,219.80 Jay
    $12,279.83 Adam Ant
    $2,467.16 Adele
    $2,197.10 Timothy
    $9,544.39 Chris
    $1,672.81 Alayne
    $1,882.02 Alec
    $10,686.28 Steve
    $80,442.70 Alex
    $35,053.66 ALLY
    $9,110.27 Alvin
    $452.42 Grayson
    $283.46 Freda
    $9,547.09 George
    $37,843.23 Trina
    $123,425.57 John

    Thanks Elaine

  13. Devi prasad says:

    please help me

    If Salary rs.10000 to 20000 in varies employees and how to insert formula for ESI 1.75% and PF 12% (It is eligible for only below 15000 not exceed 15001) Then how to insert the formula

  14. David Fay says:

    Hi Tom,

    Love your blog… it’s been a big help!

    I have two columns of data which I’m trying to find the MIN and MAX of each set (e.g. 1 x 1, 2 x 1, 2 x 2, etc. these a unit types for apartments) but each set has various square footages (e.g. 572, 676, 711, etc.). I’m just not sure how to imbed / nest the MIN or Max function within VLOOKUP or what other function would work to accomplish this task.

    Thanks,
    David

    • Tom Urtis says:

      Hi David, thank you for the kind words.
      I’m not sure from your description how your data is arranged, such as the ranges involved and what the expected results are. If you can provide more detail about those items, it sounds like a solution is do-able.

  15. Eric Bernstein says:

    Hello,
    Thank you everyone for your comments. I have been trying to create a formula to show the highest score in each game and am receiving #N/A errors. Would you be able to see where I had gone wrong?
    Thank you in advance,

    name game 1 game 2
    john 32 92
    mary 5 100
    paul 76 23
    victor 87 65
    Highest Bowler =VLOOKUP(MAX(B2:B5),$A$2:B5,0,0) =VLOOKUP(MAX(C2:C5),$A$2:C5,0,0)

  16. Tom Urtis says:

    For each of those 2 games (1 and 2) assuming they are in columns B and C respectively, and the names are in column A, try this structure:
    =INDEX(A:A,MATCH(MAX(B:B),B:B,0))
    and
    =INDEX(A:A,MATCH(MAX(C:C),C:C,0))

  17. Richard Hilburn says:

    Hi

    Is it possible to do what you have done to display the adjacent cell not just to the Max and Min values, but also the 2nd and 3rd largest and 2nd and 3rd smallest?

    thanks
    Richard

  18. Jean says:

    Hi Tom,

    I’m trying to do a combination of VLookup and Max function – there are 2 columns, column B is a list of 10 countries that repeats itself over and over, and column C is a product’s discount percentage for each of those countries. I want to find the maximum percentage discount for each of the countries, based on all the products I’m selling there, meaning that every country may have a different discount percentage. The list of countries repeats in that column for say, 5 products.

    So basically the formula needs to locate eg. Australia and all its products’ corresponding percentages, make a comparison, and return the highest percentage. Could you help me with this? Thanks in advance.

    Cheers

  19. Neelia says:

    Hey,

    I am trying to combine MAX with VLOOKUP. This is in a template doc and I need it to work with different column lengths. hopefully i can explain clearly.

    I have data in column A:1, 2, 3, 4, 3, 2, 1 (in Cell D1 there is a formula -> =MAX(A:A) Which = 4)
    I have data in column B:24,34,21,10,20,42,65
    I want to find MAX in column B up until MAX column A. So this value would be 34.
    I have tried the following formula:
    =MAX(B1:(Vlookup(D1,A1:B7,2,False)))
    This does not work and I get an error message.

    Column A might sometimes be: 1,1,1,1,1,1,8 And so I need the max array to change depending on the data on column A.

    Does anyone know if this is possible? Am I making any sense?
    Thanks

  20. Tia says:

    Tom – I have a row (H4 to R4) of times (entered like “3:10.65”) that are stored as text. I would like to find the smallest and largest times in that row but cannot seem to find a formula to do so.

    • Tia says:

      As I review my question, I would like to clarify that I do NOT have quotation marks around my entered times.

      • Tom Urtis says:

        What does the 65 refer to? Is this in milliseconds?

          • Tom Urtis says:

            I just stuck a bunch of times in range H4:N4, similar to your example and including your example.

            In another cell, I entered =MIN(H4:N4)
            And in another cell I entered =MAX(H4:N4)

            Both formulas gave me the right answer.

            What you might be missing is the formatting of those cells in H4:N4 to look like you intend for the times to be in milliseconds instead of in hours, minutes, and seconds.

            There’s a way to do it with VBA and there’s a way to do it with custom formatting without VBA.

            Assuming you want the latter (non-VBA) approach:

            Step 1 – Select your range of H4:N4.
            Step 2 – Right-click the range, select Format Cells.
            Step 3 – In the Format Cells dialog box, select the Number tab.
            Step 4 – In the Category pane, select Custom.
            Step 5 – In the Type field, enter [h]:mm:ss.000 and click OK.

            This should give you the look you want. It did for me.

  21. shiju says:

    Hai
    i have two sheets with same filed , i need to pick the highest value

    Data format is like

    Item Code #50021.A and other sheet having the same item code with different price i150 , 250, 500

    How can i pick the 500 with the help of VLOOKUP.

    Can you please help me

  22. Victor says:

    Hi,Cann you help me with the following situation: I need a formula to set up the maximum past due days for a customer, taxing into account that we know its’client code.

    Code Commitment days_past_due
    1508026 ABC Soft 2218
    1508026 ABC Soft 1991

    In this case, the maximum days should be 2218. I cannot setup a formula to do this….I’ve tried vlookup with MAX, but it was useless !
    Can you give me an advice, please ? Thank you very much !

    • Tom Urtis says:

      Assuming those items are in columns, example:

      – ColumnA ColumnB ColumnC
      1 1508026 ABCSoft 2034
      2 1508026 ABCSoft 1991
      3 1508026 ABCSoft 2218
      4 1508026 ABCSoft 2121

      This array formula would do what you want:
      =MAX(IF(A1:A4=1508026,C1:C4))

      Remember, array formulas are committed to a cell by pressing Ctrl+Shift+Enter, not just with Enter.

  23. Dave Brown says:

    Hi. Going back to the original example. How would I display multiple names if two or more people had the same maximum sales value?
    e.g. If Ella Vader also had $9,242 sales I want to display the names of Ella and Faye in E2.

    • Tom Urtis says:

      It would depend on how you want to present the names of people tied with the same max sales value. A simple lookup formula returns one lookup value but you can join as many tied lookup results as you need to, using the RANK or LARGE functions in other cells. You can know how many tied values there are with the COUNTIF function for the MAX value.

      I put some RANK examples here:
      http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-ranking-with-ties/

      As you see, this involves more than one cell to collect and then concatenate the found values — when doing this with formulas. With VBA it would be an easy matter of looping through the list for the max value and creating a string of associated names, and putting that string in the destination result cell, with no other cells or formulas involved or necessary.

  24. Dave Brown says:

    Thanks Tom

  25. Anshul says:

    Hi Tom
    in sheet 1 i have small name of any product (VIV40FH) in col A & some value in col b but in sheet 2 there is a complete name Like & LEDTVVIV40FH-LXM (characters are not fixed) & i want to pick that value from sheet 1 to sheet 2 with use of vlookup. How it will work. Pls help

  26. Ryan says:

    Tom,

    I have a set of data that I am trying to track the dates of maintenance on a fleet of equipment. Basically if Item A comes into the shop I add a row with the item name and the date. Therefore, the items repeat themselves each time it comes into the shop.

    What I want to do is have another table that just shows the fleet of equipment and the last date something came into the shop. What I tried using was a vlookup of the equipment name and then putting the max function in the column no. which did not work.

  27. Nick says:

    Hi Tom,

    This is extremely helpful. I’m wondering how you get the salesperson with the highest sum of sales if their name is listed twice in column B.

    Thanks!

    • Tom Urtis says:

      Hi Nick, see if this does what you want:

      - ColA ColB
      1 5641 Bill
      2 8081 Jack
      3 6253 Mary
      4 5024 Bill
      5 6789 Jack
      6 1417 Mary
      7 6348 Bill
      8 9925 Jack
      9 1432 Mary

      =VLOOKUP(MAX(A1:A9),A1:B9,2,0)
      Returns Jack

  28. Kokilraj says:

    WO Amt
    1 50
    2 20
    1 30
    1 40
    3 10
    2 30
    3 20
    2 40
    4 50
    5 10
    5 20
    4 60
    5 30

    1 Max of 1
    2 Max of 2
    3 Max of 3
    4 Max of 4
    5 Max of 5
    I Want Maximum of 1’s Amt one by one

    • Kokilraj says:

      WO No WO Amount
      1 50
      2 20
      1 30
      1 40
      3 10
      2 30
      3 20
      2 40
      4 50
      5 10
      5 20
      4 60
      5 30

      1 Max of 1 WO No’s Amount(to be shown)
      2 Max of 2 WO No’s Amount(to be shown)
      3 Max of 3 WO No’s Amount(to be shown)
      4 Max of 4 WO No’s Amount(to be shown)
      5 Max of 5 WO No’s Amount(to be shown)

  29. Xuseen says:

    Hello Tom

    I have multiple sheets with results of diffrent loadcases. Every sheets has 4 columns. A – node , B – Moment (M), C – Normal (N), D – Shear (Q).

    I want to make a summary of the results in one resultsheet where it shows Maximum Momement (M) and its corresponding Normal and shear.

    IE. If maximum moment in node/row 1 is found in the loadcase in sheet 4 and max moment in node/row 2 is found sheet 6 – I want the values shown in the resultsheet to be M – N – Q from sheet 4 in row 1 and M – N – Q from sheet 6 in row 2.

    Any ideas?

    Thanks!

  30. Sam says:

    I have a list of values of average sales values in which I want to classify as exceptional (above 250) high (between 250 and 150), normal (between 150 and 100) and low (below 100) using vlookup. Trying different things but can seem to get it right. I also need to use absolute referencing (??).

    Thanks

Leave a Reply

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

*