Look Who it Was Random video

Excel - Find Last Value in the Row and get the Date for that Cell - Episode 1254

659 views · Published 28 September 2010 · 3:13 · Indexed 30 September 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Once you've used the HLOOKUP to return the last value in the row, can you find the date associated with that entry? Bill shows us how to locate the last value in a row of data and return that value to our designated cell using the INDEX and MATCH Functions.

Welcome back to the MrExcel podcast. In this episode, we will be discussing a follow-up to episode 1252, where we used the HLOOKUP function to find the last entry in each row. The question that followed was, how do we find the date associated with that last entry? Well, in this episode, we will be using the INDEX and MATCH functions to solve this problem.

To start off, we will use the INDEX function to return a value from a vector, in this case, the row containing the dates. We will lock down the row using the F4 key, and then use the MATCH function to find the position of the last entry in the row. This is where the hard part comes in, as we need to return the position instead of the exact value. This is where the MATCH function comes in handy.

The MATCH function will look for a really large value in the range, and when it can't find it, it will return the last numeric value in the range. This is similar to what we did in episode 1252 with the HLOOKUP function. We will format this value as a date, and then use it as the last argument in the INDEX function to return the date associated with the last entry in the row.

This combination of INDEX and MATCH functions is a powerful tool that can solve many problems in Excel. While the MATCH function may not be used as frequently as other functions, it is definitely worth knowing and can come in handy in situations like this. Thank you for tuning in to this episode of the MrExcel podcast, and be sure to join us next time for more Excel tips and tricks.

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) Where is the last entry in this row?
(00:43) Using INDEX and MATCH to find last entry and associated date 
(02:00) Copying formula and explanation 
(03:02) 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:
Dollar sign locking
Episode 1254
Excel form
HLOOKUP
Index function
INDEX-MATCH combination
Large value lookup
Last in each row
Locking down cells
MATCH function


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

More from this channel