Mileage Calculator -- Finding lowest row matching specific values

**brine** · 07-06-2011, 09:29 AM

Hi, I'm building a spreadsheet containing vehicle service data for a group of vehicles. I have Date (A), Vehicle ID (B), Mileage (C), and Service (D).

For the convenience of people viewing this spreadsheet, I wanted to make a summary table containing each vehicle in the first column, mileage reading for next oil change, and date for next oil change. In order to do this, I need a formula that returns the mileage from the most recent mileage/date row where Vehicle ID = X and Service = "Oil Change". I have had difficulty finding the correct combinations of formulas to get this to work, and would appreciate any assistance in doing this.

In summary, the formula needs to filter out all rows where Service =/= "Oil Change", then do a further filter where Vehicle ID =/= X, then return the mileage/date data from the lowest (most recent) row. It doesn't have to necessarily "filter" the rows, I just thought that'd be the easiest way to understand my question.

**zbor** · 07-06-2011, 09:31 AM

Can you upload example workbook?

**brine** · 07-06-2011, 09:41 AM

I don't quite have enough data inputted to make an example useful, but here it is:

**brine** · 07-06-2011, 01:42 PM

The point of this function would be to quickly identify if a particular car is due for an oil change or servicing. I plan on having the main data table consist of every single maintenance work done on the vehicles, then the second data table consisting of a simple chart that displays the mileage or date that the next oil change should occur at.

So the overall idea would be something like this:
For vehicle # 293:
Mileage for next oil change = Mileage at last oil change + 4000.

To identify the mileage at last oil change, the function would have to determine the lowest row where Service = "Oil Change" and vehicle ID = "293", then return the contents of the cell in the Mileage column of that lowest row.