How do I return a cell value based on a date in another column

Dave French

I'm trying to automate a process that involves a few sheets and a form. I'm creating a fleet management process where drivers will complete a daily vehicle check out form, which will capture any issues the vehicle may have, and also the current mileage. The form is built on a master sheet which then feeds an individual sheet for each vehicle (23 vehicles in the fleet). I then have another "maintenance tracker" sheet in which I want track the current mileage vs the recommended maintenance schedule to give drivers notifications of when the vehicle needs to go in for maintenance. I'm having trouble tracking the current mileage onto the "maintenance tracker" sheet. I've tried doing a cell reference from another sheet but then it won't pull based off input date. I've moved onto creating a summary item that will capture the most recent mileage and then using a formula to capture that in the other sheet, however, I can't figure out a formula to capture the mileage in the summary. Can anyone help with the formula or give me another idea on how to reference the most recent mileage input?

Current attempt at formula: =IF(Date:Date), >=TODAY(), [Ending Mileage]:[Ending Mileage])

Katy H

Dave, to clarify, are you attempting to pull the mileage for the most recent submission?

Dave French

@Katy H , yes. There will new entries daily so I need to pull the most recent.

Katy H

@Dave French

Great, so my suggestion is to use a "Row ID" column that uses an auto-number system generated column that is composed of numbers only. Then you can use 2 separate formulas to pull the information. These should be in their own cells.

1. =MAX([Row ID]:[Row ID]) - this pulls the row id for the most recent submission.
2. =INDEX([Ending Mileage]:[Ending Mileage], MATCH([Row ID]#, [Row ID]:[Row ID], 0))

**Note: [Row ID]# is referencing the result of the formula on number 1.

Let me know if that makes sense!

Dave French

@Katy H , Thank you very much!! That worked perfectly. I appreciate your help.