Solved

# Lookup Max value with criteria

Posted on 2011-03-18
260 Views
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
Question by:Matt Lennertz
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 8

Assisted Solution

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

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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month7 days, 6 hours left to enroll