Lookup Max value with criteria

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
Matt LennertzSystems AdminAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
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
 
ragnarok89Connect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.