# highest incident in a list

#### Duane

##### Board Regular
Hey gang,

OK, so, like, here's the thing... I need a function or set of nested functions that will allow me to know what the highest odometer reading is for a particualr vehicle.

I have a list of invoices. One VIN may have been serviced two, three, four, five times. I need to be able to enter a VIN and have a formula look through the list, find the highest odometer reading for that VIN, whether the VIN shows up once, twice, three or N times.

An example...

abcde12345F6789101 8,000
SomeOtherVINs x
abcde12345F6789101 16,000
SomeOtherVINs, x
abcde12345F6789101 24,000

So, if I entered abcde12345F6789101 in some cell, the formula(s) in the column to the right should return 24,000 until another invoice with that VIN is entered, then the formula would show whatever is the highest kms.

Can this be done? Thanks much for any help provided.

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Book1
CDEFG
2253468tom
3tom150265
4****125586
5harry205825
6tom253468
7****789258
8harry365125
Sheet1

=MAX(IF(G2=C3:C8,D3:D8,0)) confirmed with Ctrl+Shift+Enter

=MAX(IF(G2=C3:C8,D3:D8,0)) confirmed with Ctrl+Shift+Enter

Thanks for the quick response, but this array formula (MrExcel likes to call these CSE formulas) produces a #NUM! error.

I like the idea though, something like this should work...any other suggestions? (I am not fluent with how to next functions in an array formula approach).

Yup, do NOT select the entire column, give it a discreet range, otherwise the CSE formula will choke. If your range changes, consider a Dynamic Range (either Named or not).

Example of Dynamic Range (not named) but also note that this depends upon both columns being the same length with no spaces in the data...
Book1
CDEFG
1tom123587
2****98658
3harry234568
4tom124758354587tom
5tom354587
6****295487
7harry125874
8tom1256
Sheet1

Yup, do NOT select the entire column, give it a discreet range, otherwise the CSE formula will choke. If your range changes, consider a Dynamic Range (either Named or not).

Excellent, mate. I did select the entire column. Fixed, and working just peachy now, thanks much.

Just out of curiousity, is there a way to do this without array formulas (like SUMPRODUCT or otherwise?) Array formulas tend to slow things down.

Thanks much.

Well, sumproduct will give you the sum of all matches... not very useful if you are looking for the maximum. You could do this in two steps:
Book1
CDEFGHIJ
1tom123587123587
2****98658
3harry234568
4tom124758124758354587tom
5tom354587354587354587
6****295487
7harry125874
8tom12561256
Sheet1

On the other hand, one array formula should not slow you down that much. Are you, perhaps, generating this output simultaneiousky for all unique owners? If that is the case, consider a pivot table instead (don't ask me, I have never tried making one, but I am pretty sure this would be a place to use one).

What else are you doing that this is slowing you down so much?

Well, sumproduct will give you the sum of all matches... not very useful if you are looking for the maximum. You could do this in two steps:
On the other hand, one array formula should not slow you down that much. Are you, perhaps, generating this output simultaneiousky for all unique owners? If that is the case, consider a pivot table instead (don't ask me, I have never tried making one, but I am pretty sure this would be a place to use one).

What else are you doing that this is slowing you down so much?

I'm managing a fleet of vehicles. Each row is one vehicle. We have approximately 370 vehicles and growing. So, the main sheet has a formula in each row. At some point, our IT may be commissioned to convert our entire database into CICS mainframe, but in the meantime I'm not conversant in Access.

Also, I've actually used SUMPRODUCT as a multiple criteria lookup, but it's straightforward (if this range matches criteria1, that range matches criteria2, the other range matches criteria3, show me what's in range 4). I was at a loss how to get SUMPRODUCT to match criteria and then give me the max in the final range.

I've actually used SUMPRODUCT as a multiple criteria lookup
Yup, that's a great use, when you get a SINGLE match, but if you have multiple records that match your criteria, you get the sum of all of those matches
I was at a loss how to get SUMPRODUCT to match criteria and then give me the max in the final range
That's because you can't. A CSE formula is the only way that I know how to do it in one single step. Perhaps Barry Houdini can come up with something (and prove me wrong as he usually does) but I can't be of any more help at this point...

I've actually used SUMPRODUCT as a multiple criteria lookup
Yup, that's a great use, when you get a SINGLE match, but if you have multiple records that match your criteria, you get the sum of all of those matches
I was at a loss how to get SUMPRODUCT to match criteria and then give me the max in the final range
That's because you can't. A CSE formula is the only way that I know how to do it in one single step. Perhaps Barry Houdini can come up with something (and prove me wrong as he usually does) but I can't be of any more help at this point...

Hey man, you've been of tremendous help, the CSE is in and working just fine. I was merely going forward for theoretic purposes and for the value of the message board, and your answer also helped in this area.

It's all good, much thanks.

Replies
4
Views
1K
Replies
10
Views
2K
Replies
0
Views
1K
Replies
11
Views
2K
Replies
5
Views
666

1,220,009
Messages
6,151,441
Members
451,028
Latest member
greekness1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back