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: , , , , , , , , , , ,
122 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.


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


    • Tom Urtis says:

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


      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:


  4. Desire says:

    You can even Look up the 2nd , 3rd,… best, or worst salesperson using :

    • 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


    • dler qader says:

      I tried this, its not working with me, could help me please

  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?

    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:


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

  12. Elaine says:

    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

    • Tom Urtis says:

      What result are you getting?

      • Diane says:

        Tom, I had a problem with the average as well. I get the same salesperson but a different amount. Anna Graham – $4,212 not $5,268. I had another set of data using these formulas, and when I sort the data the average changes. It’s really weird.

        Sales by Salesperson MIN MAX AVERAGE
        Dorah Jarre Fay Kinnett Anna Graham
        Sales Salesperson 307 9242 4212
        $307 Dorah Jarre
        $1,063 Jerry Atrick
        $1,682 Anne Teak
        $2,381 Ann Chovie The formula in cell D2 is =VLOOKUP(MIN(A4:A22),A4:B22,2,0)
        $2,658 Mike Raffone The formula in cell E2 is =VLOOKUP(MAX(A4:A22),A4:B22,2,0)
        $3,100 Sue Flay The formula in cell F2 is =VLOOKUP(AVERAGE(A4:A22),A4:B22,2,1)
        $3,943 Ella Vader
        $3,975 Barb Dwyer
        $4,212 Anna Graham
        $5,268 Anna Graham
        $5,289 Lance Lyde
        $5,997 Stan Dupp
        $7,644 Dee Lited
        $7,867 Holden Itall
        $8,213 Jim Nastik
        $8,693 Flo Tinnaway
        $8,715 Rudy Mentri
        $8,894 Jerry Atrick
        $9,242 Fay Kinnett

      • Diane says:

        Tom, I am getting a different average as well. Same salesperson, different amount. I used the formulas in a different data set and when I sort it the average changes. Any ideas?

        Sales by Salesperson MIN MAX AVERAGE
        Dorah Jarre Fay Kinnett Anna Graham
        Sales Salesperson 307 9242 4212
        $307 Dorah Jarre
        $1,063 Jerry Atrick
        $1,682 Anne Teak
        $2,381 Ann Chovie The formula in cell D2 is =VLOOKUP(MIN(A4:A22),A4:B22,2,0)
        $2,658 Mike Raffone The formula in cell E2 is =VLOOKUP(MAX(A4:A22),A4:B22,2,0)
        $3,100 Sue Flay The formula in cell F2 is =VLOOKUP(AVERAGE(A4:A22),A4:B22,2,1)
        $3,943 Ella Vader
        $3,975 Barb Dwyer
        $4,212 Anna Graham
        $5,268 Anna Graham
        $5,289 Lance Lyde
        $5,997 Stan Dupp
        $7,644 Dee Lited
        $7,867 Holden Itall
        $8,213 Jim Nastik
        $8,693 Flo Tinnaway
        $8,715 Rudy Mentri
        $8,894 Jerry Atrick
        $9,242 Fay Kinnett

        • Tom Urtis says:

          Hi Dianne – –

          I think the formula is correct the way VLOOKUP works. I looked at the data and I see that, dissecting the VLOOKUP AVERAGE formula, first, the average number for range A4:A22 is 5218. Notice that the fourth argument in the VLOOKUP formula is the range_lookup argument, which I set to 1 in the formula, as opposed to the other 2 formulas (MIN and MAX) which were set to 0. This is to be expected because in a list of numbers, you know that the min and max numbers will be an exact match, but you do not know for sure (in fact it usually is not the case) that the average number will be an exactly known number. So, with the average of 5218, the number 4212 in cell A12 comes closest to 5218 without going over 5218. In other words, in the list, 4212 is the highest number closest to the average without being larger than the average. If you were to put some other name in cell B12, such as Diane, then Diane would be returned in cell E2. All that said, it’s possible to compose a formula that returns the name of the person in column B whose number comes closest to the average number in column A, whether that number is higher or lower than the average, but the way VLOOKUP works on its own, the formula I posted does what it should, and what I’d expect. In May I might post a new blog entry to show the formula that looks up the closest number to the average. I hope that answers your question.

  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.


    • 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:

    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:

  17. Richard Hilburn says:


    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?


  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.


  19. Neelia says:


    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:
    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?

  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:

    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:

      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:

      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:


    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.


    • 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

      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?


  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 (??).


  31. SassySezzy says:

    thank you so much this formula worked for me =VLOOKUP(MAX(IF(Table1[Column2]=I24,Table1[Column1])),Table1,3,FALSE)
    thanks again

  32. ANA says:

    Hello, really helpful article. This is my formula:


    But the result is “#N/A”

    Am i doing something wrong?, could it be because the target value is not in the first column?, this is a Dashboard sheet.

    Thank you.

  33. Jason says:

    I have a large table with duplicate values in column A and a number in column B. I need to find the maximum value of B for each value in A. In column C, I have a list of everything that’s in A, but with duplicate values removed. So it looks like this:

    – ColA ColB ColC ColD
    1 AAAA 153 AAAA
    2 AAAA 94 BBBB
    3 AAAA 2000 CCCC
    4 AAAA 94 DDDD
    5 BBBB 1867
    6 CCCC 7
    7 CCCC 431
    8 DDDD 816
    9 DDDD 900
    9 DDDD 900

    I need column D to have 2000 in row 1, 1867 in row 2, 431 in row 3, and 900 in row 4. For now, it looks like if I sort the table with Column A from A to Z, then Column C smallest to largest, it’ll return what I need (automatically giving me the value in column B that matches the final instance of column A) if I just use =VLOOKUP(C1,A$1:B$9,2). However, it’s not always going to be possible to sort that way. Is there a different formula that I should be using?

  34. Vikram Kumar says:

    Sir i need maximum of criteria By V lookup

  35. Othon says:

    Hello Tom, thanks so much for your info. I think the major problem on using VLOOKUP is the value approximation. When I tried TRUE, I got a wrong value, but FALSE got the right answer. Kinda of strange (true=fase or false=true), but it worked to me. BTW, there’s a Excel formulae translator website ( which you can find the correct formula to any language.

    Cheers, mate!

    • Tom Urtis says:

      Thanks for your comment and the translator website.
      Regarding VLOOKUP’s range_lookup argument, you might like my 1-minute video about that topic in my Excel It In a Minute video series.

  36. Mourad Louha says:

    Hi there,

    thanks @Othon for recommending my Excel Formula Translator 🙂

    The site include an online tool for translating formulas too, where you can e.g. select the Excel version, the source and the target language:

    Note: if the source formula includes array constants, such as {1,2,3}, the translated row and column separators should be verified after the translation, as the tool currently does not always recognize them correctly yet. I am working on this.


  37. Duncan Williamson says:

    Excellent: this simple things one never thought about. Works perfectly.

    As an aside, I transposed my table data and used HLOOKUP and it works just as well. In this case, for the AVERAGE version, we sort the data by using the Sort left to Right function in the Sort & Filter option

  38. Muthu says:

    I have sales data of employees day wise for three months. I need to calculate min, max & average sales day wise. Could you guide on how to go about?

    Is there a way to use Vloop up function and calculate this efficiently than using min & maximum for every day.

    • Tom Urtis says:

      Can you please clarify, is it that you want to look up a particular date, maybe from a list of date in column A, and you want to return some value on that corresponding lookup date’s row that resides in another column, such as maybe column C? Or is it that you want to return all values from a particular date and that particular date is repeated more than once in column A, in which case a filter might be your better bet.

  39. P.B.Sadi says:

    Hi Tom
    Can you help me on this?
    I have A column including :book1,book2,book3,book1,… (I mean I have a name repeated because of several prices)
    and B column including books prices and i want to identify the Min Price how can I get on it?

    • Tom Urtis says:

      You can do this with an array formula.
      For example, given the below data, the formula in cell D1 that returns 15997 is
      Remember, an array formula is committed to a cell with Ctrl+Shift+Enter, not just with Enter.

      X Col A Col B Col C Col D
      1 Books Price Book2 15997
      2 Book2 21548
      3 Book1 32654
      4 Book2 52146
      5 Book1 82514
      6 Book2 15997
      7 Book3 21447
      8 Book1 56228
      9 Book2 33677

  40. Ella says:

    Hi Tom,
    Thanks for the tip!
    I was wondering on how you would use the Max or min function if you want to look from right to left?
    I’m trying to use the choose function but it doesn’t seem to work for me…
    =VLOOKUP(CHOOSE({1,2},MAX(C15:C38), B15:B38), 2, FALSE)
    I can’t use the index or match functions because the lookup values will not match each other.
    I would be grateful for your assistance.

  41. Disha Mahajan says:

    I want to use vlookup with min function between two sheets

    I want to get values from one column for particular value in another column and then the values which I will get, I have to get the min of that values in other sheet. Can you suggest me on what can be done?

    • Tom Urtis says:

      Can you provide actual cell references and sheet names as an example of what and where your data resides, and especially with that example, your expected results. I’m not clear on exactly what you are asking; maybe you need a filter instead of a lookup formula because you wrote “…and then the values which I will get, I have to get the min of that values in other sheet.”. Or maybe you need an array formula.

  42. Ghizali says:

    Hi Tom,

    Need a solution to my problem,
    I have a range of names with multiple entries with their aging, I need to call the maximum of age from multiple ages of a person. (e.g MR A has documents pending from 8 days, 20 days & 30 days) I need to report the maximum days of every person.

    How can I use VlookUp or any other function to call max days for each person (e.g Need max days of Mr. A from range of Mr A to Mr Z with multiple entries of each person)

    Thanks in Advance


    • Tom Urtis says:

      Without knowing any ranges of you data (because you did not say what it was), you can establish a list of unique names from the original data range like the picture.

      The array formula in the blue cell D2 copied down to cell D4 is

      The array formula in the orange cell E2 copied down to cell E4 is

      Remember, these are array formulas. You commit them to a cell by pressing Ctrl+Shift+Enter, not just with Enter.

  43. Swamy M N says:

    i need formula for result between two number

    we are taking loan from one company the company is giving loan and taking service charges like for 1 lakh 1% of the loan amount and minimum Rs.2500 and mximum Rs.5000 here for 1 lakh the result is 1000, But the company is taking minimum 2500. for this what is the formula. means i need result between 2500 and 5000 if result 1000 also it has to be show Rs.2500 and for 6 lakh the result is 6000 but result has to be show maximum 5000 and result between 2500 and 500 has two be show actuall result,
    please guid in this matter

  44. Mark says:

    I’m attempting to do vlookup across multiple sheets using =vlookup(Average

    • Tom Urtis says:

      A bit of further explanation will help with a relevant possible solution. For example, what if more than 1 such value exists in a sheet’s list. And is every sheet (every means every) involved, or only some sheets, and also on whatever sheets are involved, is it always the same range of data where the lookups need to occur.

  45. I Need to find Only Max Number against each Name. says:

    Name Call Ageing Name Max Ageing
    A 1 A ?
    A 2 B
    A 3 C
    B 4 D
    B 5 E
    B 6 F
    C 7
    C 7
    D 3
    E 8
    E 9
    F 10

    • Tom Urtis says:

      Your question sounds like this example that I have previously posted:

      • Michelle says:


        Could you please advise how can I apply above formula to find multiple values for one item?

        Specifically, in the example below each user has multiple versions but I would like to retrieve the tasks only for the latest version (6 and 2). I was able to retrieve one task but the User has 1+.

        VERSION USER TASK User Version TASK1 TASK2 ………
        1 User1 000000CG User1 6
        1 User1 000000CM User2 2
        2 User1 000000CG
        2 User1 000000CM
        3 User1 000000CG
        3 User1 000000CM
        4 User1 000000CG
        4 User1 000000CM
        5 User1 000000CG
        5 User1 000000CM
        6 User1 000000CG
        6 User1 000000CM
        1 User2 000000BG
        1 User2 000000BN
        1 User2 000000BV
        1 User2 000000BW
        2 User2 000000BM
        2 User2 000000BN
        2 User2 000000BV
        2 User2 000000BW

        Thank you.

        • Tom Urtis says:

          Not exactly sure I am following with regards to what you expect the final result to be. Is it a number of tasks per user per version, or is it a list of version names or user names.

          • Michelle says:

            Hi, thanks for the reply. I am expecting to get a number of tasks per user for MAX version. In my example :
            For User1 :
            6 User1 000000CG
            6 User1 000000CM

            For User2, I would get the below results :
            2 User2 000000BM
            2 User2 000000BN
            2 User2 000000BV
            2 User2 000000BW

            • Tom Urtis says:

              If you are familiar with pivot tables, I would first suggest that if you are VBA-reluctant, although that will get you to a point where you still need to take the list off that, which is not a big deal, just an extra step. If it were me, assuming you have more data to process than this short example, I would use a macro to list those. It depends on your familiarity with code and especially if (just guessing ’cause I have no idea) your employer prohibits macros if this is a workplace task, but shame on any employer who does that.

  46. says:

    Hi, I’m trying to vlookup a MAX number in a pivot table.
    I wrote a function like this: =VLOOKUP(MAX(B4:B15),A4:B15,1,FALSE)
    But I get “#N/A” error I guess it is because I’m trying to VLOOKUP a MAX in PivotTable and not just a set of data? Please someone advise if my guess is correct and how to fix it. I appreciate it in a advance. Thank yoU!

  47. Elle Vee says:

    Hi-Might someone be able to share a way to return a date (“Record Date” in below example) from spreadsheet 2 that falls between two dates on spreadsheet 1 when the identifiers match?
    Spreadsheet 1 has:
    ID Start Date End Date Record Date
    1 8/31/20 9/4/20
    2 9/1/20 9/6/20
    3 9/7/20 9/8/20
    4 9/13/20 9/20/20

    Spreadsheet 2 has:
    ID Record Date
    1 4/5/20
    1 7/6/20
    1 8/4/20
    1 9/3/20
    2 5/5/20
    2 9/3/20
    3 8/6/20
    4 9/14/20
    4 10/4/20

    For each ID in spreadsheet 1, I would like to show the date in spreadsheet 2 – if any – that falls between the start and end dates on spreadsheet 1. What formula could I use to do this?
    Thank you!

    • Tom Urtis says:

      Must this be a formula solution of is VBA an option. The formula can be a burden on the workbook depending on how many rows of data you actually have.

      • Elle Vee says:

        Hi, Tom – Thank you for replying to my question. I would think VBA can be an option (although I never have used visual basic). I do have the Developer/Visual Basic function in Excel.

  48. Owen Letuka says:

    Hi, I’m trying a vlookup with max/min and I want the max/min to display column 1 (index_number) on my array.

    I tried this:


    Can you kindly tell me what could be wrong with this formula?

  49. prateek says:

    I need to find min of first 88 values in a column and then for next 88 and so on for 250 times. What should I do.

    • Tom Urtis says:

      Do you want a formula solution or a VBA solution, and how and where exactly would you want to list these min and max findings, and what is the range of cells where all this data is.

  50. Rcbert says:

    Hi Tom!

    I am working with the current cell formula … =VLOOKUP($E102891,Sheet1!$E$2:$AI$1048576,11,0)
    I am trying to replace that 1048576 with a formula that finds the last used row in a column in hopes of speeding up the calculation.
    The current last row is about 105000, am I assuming correctly that if the last row used replaced that 1048576, the calculation would be roughly 10 times faster?

    I have tried such things as …
    =VLOOKUP($E102891,Sheet1!$E$2:”$AI$” & LOOKUP(2,1/(A:A””),ROW(A:A)),11,0)
    but excel doesn’t like it.

    If it matters, this formula is in a different sheet, thus the Sheet1! reference in the formula.

    • Tom Urtis says:

      What is an example of a lookup value in cell E2
      what kind of data (text or numbers) is in column O of Sheet1 assuming that is the column you want to look up because it is 11th column from and including column E.

  51. Rcbert says:

    Thank you Tom!

    The E column data always appears to look like …

    example E2 = D0188210100225

    The O column values in Sheet1 are numeric integers
    example Sheet1 O2 = 865858

    • Tom Urtis says:

      Something is wrong, maybe my interpretation of what you are doing. Why do you want to look up a text value in a column only containing numbers.

  52. Rcbert says:

    Sorry Tom. Perhaps I was not clear.

    I will start over if you won’t mind.

    I am working with the current cell formula … =VLOOKUP($E102891,Sheet1!$E$2:$AI$1048576,11,0)

    $E102891, in Sheet2, contains the data of ‘D0188210100225’
    $K102891, in Sheet2, contains the formula =VLOOKUP($E102891,Sheet1!$E$2:$AI$1048576,11,0)

    The VLOOKUP finds a match of ‘D0188210100225’ in Sheet1 @ $E30162. It then looks to the O column, as you mentioned, and finds the value of 865858. It then displays that found ‘865858’ into the cell containing the formula … $K102891, in Sheet2.

    • Tom Urtis says:

      I know the count of used rows can change, but just generally, about how many rows really are involved. Maybe your data set is legitimately as voluminous as being over 100,000 rows, but that is rare so I wonder why your VLOOKUP formula is almost at the very bottom of the excel grid.

  53. Rcbert says:

    There are several thousand vlookups, i posted just one example. They all have that 1048576. I didn’t write it, so I can’t answer to the explanation.

  54. George says:

    Having problems figuring this one out. It is picking up first character in MIN formula, should be showing $1 not $2. Any idea’s?

    12:04pm (B1)

    Time Price
    12:04pm $2.00
    12:04pm $3.00
    12:04pm $1.00
    12:04pm $1.00
    12:05pm $1.00 MIN PRICE $2.00


    • Tom Urtis says:

      Are those dollar signs part of a text entry that you manually entered along with the number?
      Also, why do you think that the formula should be returning $1 not $2? If the time entries are in order of entry which it looks like, then $2 is the correct returned value because $2 is the first entry next to 12:04 PM. Not because it is the first entry, but I bet that the seconds (not showing in your post here) is fewer in the 12:04 minute than the later seconds in the 12:04 minute. Your underlying times in column C are probably like this:

      Time Price
      12:04:06pm $2.00
      12:04:17pm $3.00
      12:04:38pm $1.00
      12:04:54pm $1.00
      12:05:29pm $1.00

  55. Bruno says:

    Thanks for being so helfull. I am still strugilling with this. I have a form that constatly insert new rows from employees everytime they run it. I am trying to create a quick view to see when was done the most updated entry.
    So I have on TAB for Task1 another TAB for Task2 and one Tab named Dashboard where I want to see the name of the employee based on the DATA from Task1 and Task2 with the most recently entry date.

    I tried several of your examples but still can not make it work between Vlookup, Index/match and Max.

    • Tom Urtis says:

      I’m trying to visualize your workbook design based on your description but (at least to me) it is not clear.

      What exactly does this mean?
      “I want to see the name of the employee based on the DATA from Task1 and Task2 with the most recently entry date.”?
      Where is DATA,
      Where on the Task1 and Task2 worksheets are the entry dates,
      Where is the lookup criteria of the employee name
      Are you saying that the same dates for the employee of interest are the last date entries on both the Task1 and Task2 tabs?

      If you can upload a picture that would be great. Otherwise, posting on, which I also contribute to but so do many other Excel experts around the clock, might provide you with a faster response.

Leave a Reply

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