MAX formula-highest value per card #

[MikeInKansas]

i get a XL download from our vehicle fuel cards, and each "row" is a fuel fillup, per Card number, and also a Mileage at that time-the MAX function gives me a number for the entire download, but i need a Maximum for ONLY those records that match a specific card number :
Card # Mileage

0172 - 1 161,211
0172 - 1 188,490
0172 - 1 107,715
0172 - 1 136,297
0172 - 1 188,333
0172 - 1 142,239
0172 - 1 13,730
0225 - 1 10,069
0225 - 1 171,296
0225 - 1 70,290
0225 - 1 61,354
0225 - 1 66,311
0225 - 1 13,530
0225 - 1 111,996
0225 - 1 211,295
0225 - 1 173,141
0225 - 1 161,136
0225 - 1 178,636

Need Highest Mileage By Card

0172 - 1 _____________
0225 - 1 _____________

This seems like a "2-dimensional" lookup..
Thanks for any help !
Mike

 

[rs2k]

Could you not just sort them?

 

[MikeInKansas]

actually, i get over 600 records each month-we have 75 vehicles
i just selected a sample for the post
but yes, i could easily sort the records by card number, then mileage
but i dont want to then find and write down the ending mileage!
Thanks

 

[ChrisOswald]

array formula to get the max for value "0172 - 1"
Replace a$1:a$18 (the range of the Card number) and b$1:b$18 (the mileage) with the appropriate ranges.

=MAX(IF(A$1:A$18="0172 - 1",B$1:B$18,""))

use control-shift-enter to enter it. Excel will pull curly braces around it.

There's some pretty good info out on the web if you want try searching for "Excel Maxif" or "Excel Minif" -- there's some traps for the unwary user using these types of formulas.