**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.

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

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.

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

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.

This is not working for me also 🙁

“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.

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.

Ute-S: Thank you for your useful comment, much appreciated!!

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

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)

Hello Mindy, thanks for following my Excel blog page. See if the two examples in this link help you for how you can do this. Just modify the formula’s ranges to fit your situation.

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-lookup-by-min-or-max/

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)

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

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

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.

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.

See if one of these links help:

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-lookup-with-multiple-criteria/

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-intersecting-lookup-with-multiple-criteria/

http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-lookup-by-min-or-max/

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

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.

Good job Tom Urtis… 🙂

Vlookup only works from left to right…

No, VLOOKUP can work from right to left. See my example here:

http://www.atlaspm.com/toms-tutorials-for-excel/tom%E2%80%99s-tutorials-for-excel-using-vlookup-for-reverse-lookups/

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), “”))

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.

Hi

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

The order of data has no bearing on the MAX function, unless there are conditions in the formula that you did not explain in your post.

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

What result are you getting?

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

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

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.

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)

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))

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

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

I was away for a week; is this still an open item?

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

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.

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

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

yes

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.

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

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 !

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.

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.

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.

Thanks Tom

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

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.