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

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])


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!