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
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
-
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:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920