Get the Last Value in a Row - Excel Formula

In this video, we look at how to get the last value in a row using a formula. In this example, the last value is the current week's data.

We always want to see the current week, even as time goes by and more weeks are added. This formula dynamically returns the last value in the row even as it expands

An example is shown for a list of values, then it is taken further into a reporting scenario and running from a drop-down list.

Here are the timings of the video.
00:00 - Introduction
00:43 - Formula to get the last value in a row
03:12 - Return the second from the last value
04:03 - Test the formulas for the last two values
04:29 - Return the last value for a specific product

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[mestergabbe]

Thank you!

Im new with google sheets/excel. Been struggling with this formula, finally i have it

[brauliogarcia]

I have been looking for this solution for a long time, thank you!

[geoffh]

Always great to watch, very helpful instructive and well explained. A definite 'go to' resource

[sarathganesh]

Hi!
A very crisp video. Thanks for taking the time.
Can you shed a little insight on how to use this for data that are in a single column? Fetch the last entry in column D, for example. I tried =Index(Range), 1, 0 and many variants to get it to work but with no luck

[lynxwomancat]

Genius!! Just solved an issue that will save me a lot of work!!! I no longer have to change the formula, each month, to the newest populated column. Virtual fist bump, Alan!

[wayneedmondson]

Hi Alan.. INDEX, MATCH and COUNTA to the rescue. Excellent! Your clever examples and solutions are teaching my brain how to solve many problems. Thanks and Thumbs up!

[ruthrobinson]

This has worked well for me, thank you. Only problem is I have two cells that are coming up blank - nothing in them. They have exactly the same calculation as all the others.

[nidhikadam]

Hi! Great video and very helpful. Just the solution I was looking for. It worked on most of my data but in a few rows, it did not pick the last value and instead displayed a zero. How can I correct this, any suggestions?

[beccachavez]

Thank you so much for this video!! <3

[UrosDavidovic]

Very helpful! My case is a little different, but it should work, I have a string of values in 8 columns of a row, and need to figure out the last value that is not a number and not 0. So, essentially last number > 0. I am guessing the COUNT criteria just changes.

[bramstebbings]

@computergaga Thanks for what you do! One thing about google sheets... for any array formulas like this you must add the array formula function. then you're off to the races.

[jennifergodreau]

This is very helpful!!
Question:
How would you handle roll over numbers using these formulas? For example, let say Product E has rolled over after reaching 1000, thus your value for week 22/11/2018 is 153 instead of 1153. Now, you want to calculate the increase between this week and last week. How would you account for this in your formula? I'm doing something similar for meter readings and they roll over at a certain point. Thank in advance for any recommendations you can offer:)

[nielsnielsen]

yes okay, the formula is working now. thanks.

[Whitegatellc]

Alan, this video was really helpful. What if I want the column header (Date in your example) returned for the last, non-blank cell in each row? Do you know how I can setup the function? Thanks for your anticipated assistance.

[kailinn]

Hi Allan, thank you so much. I was wondering if I want the date of the last occurring numbers how do I change the formula? Because some of the date will not have data in it.

[houssamHi]

Great description sir, you should get 10 likes

[osamasherwani]

Thanks for the sharing, can you please let me know if there is 0 in data & we need to find data instead 0, what would be the formula?

[THEGLADIUS]

Hey Allan what is the logic of counta in the index column section that lets you capture the last value?

[dbcooper]

I'm a big fan of you after watching this video

[sourabh]

Hi Allan, Good Job But I have one query if I want above mentioned Date instead of Numbers which formula is suitable can you please share Formula..