Look Who it Was Random video

Excel - LOOKUP Function: Find the Last Match in Excel with this Clever Formula! - Episode 1083

319 views · Published 19 August 2009 · 3:16 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  LOOKUP Function: Find the Last Match in Excel with this Clever Formula!

Welcome back to the MrExcel netcast! In this episode, we're going to tackle a problem that was brought up in episode 1073 by Sarah from a cattle farm in England. She needed help finding the last match in a set of data, specifically the last time a vehicle was fueled. With the use of a clever formula from Daniel in Quebec, we were able to solve this problem using the LOOKUP function. But how does this formula actually work? Let's dive in and find out.

First, we start with a large set of data and the task of finding the last match. This can be a daunting task, but with the help of a pivot table, we can easily analyze the data and find the solution. In this case, we are looking for the last time a vehicle was fueled, which is represented by the mileage in the data set. With the use of the LOOKUP function, we can easily find the last match and copy it down for all the vehicles.

Now, let's take a closer look at the formula provided by Daniel. At first glance, it may seem confusing and impossible to work, but with the help of the Evaluate Formula tool, we can break it down and understand how it actually works. By evaluating the formula step by step, we can see that it is comparing the vehicle in question to all the other vehicles in the data set and generating a series of TRUEs and FALSEs. But the real magic happens when we divide 1 by this array of TRUEs and FALSEs. This results in a series of DIV|0| errors and occasional 1s. By finding the last 1 in this array, we can use the results vector to give us the corresponding row, which is the last match we are looking for.

I have to say, I am impressed by this formula and its ability to solve such a complex problem. It truly is like magic and I want to thank Daniel from Quebec for sharing it with us. If you don't have an Excel Master Pin, Daniel, please reach out to me and I will gladly send one to you in Canada. And for everyone else, thank you for tuning in to this episode of the MrExcel netcast. Don't forget to like, comment, and subscribe for more Excel tips and tricks. See you next time!

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) Finding the last match in a dataset 
(00:34) Using a formula to find the last occurrence 
(01:00) Evaluating a formula using the LOOKUP function 
(02:03) Understanding how the formula works 
(02:34) The magic of the formula 
(02:45) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Division by zero errors
Evaluate formula
Excel master pin
Last match formula
Lookup function
Match function
Mileage calculation
Pivot table
Results vector
True and false values
Vlookup vs Lookup


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


Daniel from Quebec sends in a wild formula to solve the Last Match problem from Episode 1073. We'll look at using Evaluate Formula to study how the LOOKUP value actually works. Episode 1083 shows you how.

More from this channel