Solved

Lookup Max value with criteria

Posted on 2011-03-18
2
255 Views
Last Modified: 2012-05-11
i'm trying to pull back the maximum mileage on a vehicle from a fuel system dump.

The data is on the left of the screen shot, the summary page is on the right.

i need to do a lookup using the unit number (truck number) from the summary to the data to return the max mileage (current mileage) on each vehicle.

=MAX(VLOOKUP(B4,DataDump!C:F,4,FALSE))  only returns me the first value on the mileage for that truck number not the greatest.

any help would be greatly appreciated.


screenshot.bmp
0
Comment
Question by:Matt Lennertz
2 Comments
 
LVL 8

Assisted Solution

by:ragnarok89
ragnarok89 earned 250 total points
ID: 35165312
I believe you need an array formula,

={MAX((C2:C100=$B$3)*(F2:F100))}

Type the formula without {}

press Ctrl-Alt-Enter, this will save the fmla as an array fmla, and add the {}
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 35165516
Not for points

ragnarok89's solution will work fine for MAX but if you want MIN too then the same approach won't work.......so I prefer this syntax

=MAX(IF(Datadump!C$2:C$100=B4,Datadump!F$2:F$100))

confirmed with CTRL+SHIFT+ENTER

Now if you want MIN you can use exactly the same syntax.....but with MIN in place of MAX

regards, barry
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel VBA - copying formats 7 22
Excel file merge 2 cells with line break 2 23
Macro 3 20
Add macros on Open 9 32
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now