Excel - Find Last Value in Each Row in Excel - Episode 1252

1,655 views · Published 24 September 2010 · 3:34 · Indexed 31 October 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Find the last value in each row in Excel.

Welcome back to the MrExcel podcast! In this episode, we will be discussing how to find the last value in each row of an array. This is a common issue that many Excel users face, and we have a simple solution for you.

First, let's take a look at the problem. You have a large array of data, with values spanning across multiple rows. However, you need to find the value from the latest week for each item. The challenge here is that some items may have data for all weeks, while others may have missing data. So, how do we find the last value in each row?

The solution lies in using the VLOOKUP function with the "true" option. This version of VLOOKUP allows us to find the value just less than the one we are looking for. So, even if the exact value is not found in the table, we can still retrieve the last value that affects the row. This is a handy trick that can save you a lot of time and effort.

But what if we want to find the last value in a column instead of a row? In that case, we can use the HLOOKUP function with the same "true" option. This will allow us to find the last non-blank value in the column. So, whether you need to find the last value in a row or a column, the VLOOKUP and HLOOKUP functions with the "true" option have got you covered.

I hope you found this episode helpful. If you have any questions or suggestions for future episodes, please send them to [email protected]. And don't forget to check out our sponsor, MrExcel.com, for all your Excel needs. Thank you for tuning in to the MrExcel podcast, and I'll see you next time for another informative episode.

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) Find last value in each row
(00:37) Different number of values in each row
(01:01) How Approximate Match VLOOKUP works
(01:40) If there is no number less than the lookup value
(02:14) HLOOKUP a very large number to solve
(02:56) Testing with new data
(03:10) 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 #excelvlookup #lookup #xlookup

This video answers these common search terms:
find last value in excel row
excel formula to identify the last entry in each row
extract the last data point in excel row
last non-empty cell in excel row
get the final value from each excel row
find the rightmost value in excel row
determining the last entry in excel spreadsheet rows
excel formula to locate the end value in each row
retrieve the trailing value in excel row
identify the tail value in excel row

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

A question from my seminar at the NAESA conference in New Orleans (September 2010): "How can I return the last value entered in a particular row?" Episode #1252 will show you how.

More from this channel