Excel - Excel Gurus Gone Wild: Solve a Tricky Problem with VLOOKUP & MATCH Function - Episode 1073
1,073 views · Published 9 August 2009 · 5:01 · Indexed 12 November 2026
Channel: MrExcel.com · 2009 · Education
Microsoft Excel Tutorial - Excel Gurus Gone Wild: Solve a Tricky Problem with VLOOKUP & MATCH Function. Sarah from the cattle farm in the UK writes in again with an insanely difficult question. Do a VLOOKUP, but find the last previous entry that matches the vehicle. Episode 1073 shows you a formula from Excel Gurus Gone Wild to solve this problem. Welcome back to the MrExcel netcast, where we tackle all things Excel. I'm Bill Jelen, and in this episode, we're going to dive into a tricky problem sent in by one of our viewers, Sarah. She's a cattle farmer in England and as a thank you for answering her previous question, she's named one of her new cattle after me. Yes, you heard that right, there's a cow named after me somewhere in England. But let's get back to the problem at hand. Sarah's question involves finding the last entry for a specific vehicle and bringing that mileage down to a designated cell. Currently, this is being done manually, but I have a formula that can solve this problem. However, it's a bit complex, so let's break it down step by step. First, we'll use the MATCH function, which is similar to VLOOKUP but with a twist. We'll use the exact MATCH, which tells us the position of a specific value in a sorted range. But here's a cool trick - if we use a really large number as the lookup value, it will always return the last cell in the range. Neat, right? Now, let's make things a bit more interesting. We'll replace some of the numbers in the range with N/A values. Surprisingly, the MATCH function ignores these N/A values and still gives us the position of the last real number in the range. This will come in handy for our solution. Next, we'll use an array formula to create an array of either 1's or N/A's. This array will check if the values in the range above the designated cell match the value in the designated cell. If they do, it will return a 1, if not, it will return an N/A. Then, we'll use the MATCH function again, this time looking for a really large number within this array. This will give us the position of the last matching number, which is exactly what we need. Finally, we'll use the INDEX function to retrieve the fuel value from the designated column and the row number given by the MATCH function. And voila, we have our solution! We'll copy this formula down and see how it matches up with what Sarah had manually done. And it works perfectly, except for the N/A values which indicate that those vehicles have not been fueled in the given data set. This formula may seem complex, but it's just one of the many amazing formulas featured in our book, Excel Gurus Gone Wild. This book is a compilation of some of the best formulas from the MrExcel message board, and I highly recommend it for anyone looking to take their Excel skills to the next level. I want to thank Sarah for sending in this challenging question and for naming a cow after me. It's always great to hear from our viewers and see how they're using Excel in their daily lives. And who knows, maybe one day I'll have a whole herd of cows named after me. Thanks for tuning in to this episode of the MrExcel netcast. Don't forget to like, comment, and subscribe for more Excel tips and tricks. And if you have a question or a formula you'd like to share, leave it in the comments below. Until next time, this is Bill Jelen signing off. See you in the next 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) Introduction (00:15) Strange Email from Sarah (01:21) Explanation of the Problem (02:14) Using MATCH Function (03:11) Using INDEX Function (04:08) 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: Array formula Cattle farm Data analysis Excel formulas Excel Gurus Gone Wild Excel tips and tricks Fuel mileage INDEX function MATCH function Pivot table VLOOKUP Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152560/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:37
Excel - Enhance Your Excel Charts: Add a Picture as a Background in Excel - Episode 917
-
2:00
Excel Hiding Data in Plain Site - Episode 919