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
I tried this, its not working with me, could help me please
“it’s not working” tells me nothing about what you tried or what your data is like. Please post back with what that something is and describe what result you are getting that is not what you expect.
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?
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
Hello Diane – – See my reply to your other post.
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
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.
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.
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!
Hi Nick, see if this does what you want:
- ColA ColB1 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
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
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)
What do all those WO’s mean?
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!
Is a formula solution the only option for you, or are you willing to accept a VBA solution if that is deemed easier and more efficient?
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
See if this formula works, assuming your sales values start in cell A1.
Notice where the semicolons and apostrophes are in the formula.
The formula works when I tested it.
=VLOOKUP(A1,{99,”Low”;150,”Normal”;250,”High”;1000,”Exceptional”},2)
See my blog post here for more examples of lookup formulas that do not require a lookup table:
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-vlookup-without-a-lookup-table/
thank you so much this formula worked for me =VLOOKUP(MAX(IF(Table1[Column2]=I24,Table1[Column1])),Table1,3,FALSE)
thanks again
You’re welcome — happy to have helped!
Hello, really helpful article. This is my formula:
=VLOOKUP(MAX(D73:D139),B73:D139,2,0)
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.
Without seeing your worksheet and knowing where things are, should the 2 be a 3 if the third relative column (column D in this case) holds the value you want to return?
Just try to write as,
=VLOOKUP(MAX(D73:D139),B73:D139,3,0) It may help….
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?
If your data is laid out as you say, then in cell C1 apply the array formula
=MAX(IF($A$1:$A$10=C1,$B$1:$B$10))
and copy down to cell C4.
Note, this is an array formula.
You apply it to a cell by pressing Ctrl+Shift+Enter, not just with Enter.
Thank you, works perfectly!
Sir i need maximum of criteria By V lookup
Can you provide any details to explain further what you are working with.
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 (https://en.excel-translator.de/functions/) which you can find the correct formula to any language.
Cheers, mate!
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.
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: https://en.excel-translator.de/translator/
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.
Best,
Mourad
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
Thanks, Duncan!
Hello,
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.
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.
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?
You can do this with an array formula.
For example, given the below data, the formula in cell D1 that returns 15997 is
=MIN(IF(A2:A9=C1,B2:B9))Remember, an array formula is committed to a cell with
Ctrl+Shift+Enter, not just withEnter.X Col A Col B Col C Col D1 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
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.
Thanks,
Ella
Hello Ella, see if this picture helps you.
Hi Tom,
Yes, thanks for the tip! I was able to solve my problem. 🙂
Many thanks,
Ella
Thanks for the follow up!
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?
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.
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
Regards,
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
=INDEX($A$2:$A$18,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$18),0))
The array formula in the orange cell E2 copied down to cell E4 is
=MAX(IF($A$2:$A$18=D2,$B$2:$B$18))
Remember, these are array formulas. You commit them to a cell by pressing Ctrl+Shift+Enter, not just with Enter.
i need formula for result between two number
Ex..
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
Hello Swamy – –
I was out of town, traveling on business and just saw this. Is this still an open item or have you found a solution?
OK sir no problem sir, I got solution for this thank you for reply sir.
I’m attempting to do vlookup across multiple sheets using =vlookup(Average
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.
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
Your question sounds like this example that I have previously posted:
Hi,
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.
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.
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
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.
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!
I’m guessing based on your description that you might need the GETPIVOTDATA function to incorporate into your formula. I put a post on my blog about that a few years ago, the pictures are a bit dated but the message is the same, about how to use that function the way I think you are talking about. If this is not any help, please post back.
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-toggling-the-getpivotdata-function-on-and-off/
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
Etc.
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
Etc.
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!
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.
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.
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:
=VLOOKUP(MAX(column:range),table_array,1,FALSE)
Can you kindly tell me what could be wrong with this formula?
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.
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.
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.
What is an example of a lookup value in cell E2
and
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.
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
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.
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.
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.
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.
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
=MIN(VLOOKUP(B1,B4:C8,2,FALSE))
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