Look Who it Was Random video

Excel - How to Find the Last Mileage for a Specific Vehicle | Brilliant Formula - Episode 1084

303 views · Published 20 August 2009 · 2:36 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  How to Find the Last Mileage for a Specific Vehicle | Brilliant Formula.

Welcome back to the MrExcel netcast! In this episode, we're going to tackle a common problem faced by many Excel users - analyzing massive amounts of data. But don't worry, we've got a solution for you.

In our previous episode, we discussed a problem sent in by Sarah from a cattle farm in England. She needed to find the last match for a vehicle's mileage. And now, Jamie from England has come up with a brilliant formula to solve this problem. Let's take a closer look at it.

Jamie's formula involves using two arrays - one to check everything in column A and another to multiply the TRUEs and FALSEs with the corresponding mileage in column B. This way, we can easily identify the last mileage for a specific vehicle. And the best part? We don't get any N/As when a vehicle has not been previously fueled. Instead, it shows up as 0, making it more user-friendly.

But wait, there's more! Jamie also suggested that we assume the data is in some sort of date sequence, meaning the mileage for a fueling event should always be higher than the previous one. So, we can simply use the MAX function to find the highest mileage in the array. And with the help of [Ctrl Shift Enter], we can get the desired result - the last mileage for a specific vehicle.

I hope this solution helps you in your data analysis tasks. And a big thank you to Jamie from the U.K. for sharing this formula with us. If you have any other Excel tips or tricks, feel free to share them with us in the comments below. And don't forget to hit that like button and subscribe to our channel for more helpful Excel tutorials. See you next time on the MrExcel netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Solution to episode 1073
(00:20) Another solution from Jamie in England
(00:30) Explanation of the formula
(01:18) Assumption about date sequence
(01:32) Using MAX function to find highest mileage
(01:50) Copying the formula
(02:06) Benefits of using this formula
(02:16) Clicking Like really helps the algorithm

This video answers these common search terms:
Excel formula to find highest mileage
Excel tips from Jamie in the UK
Finding mileage for a specific vehicle in Excel
Formula for finding last mileage in Excel
Handling N/A values in Excel formulas
MrExcel netcast episode 1073 solution
MrExcel netcast episode with Jamie's formula
Using arrays in Excel formulas
Using Ctrl Shift Enter in Excel formulas
Using the MAX function in Excel
Using TRUE and FALSE in Excel formulas
YouTube video on analyzing data with pivot tables

Jamie from the UK sends in yet another formula to solve the Last Match formula from Episode 1073. Episode 1084 shows you how.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152569/

More from this channel