Finding the most recent value in a row on a spreadsheet

Other Questions Google Sheets
1

Hey everyone, I’m working on a spreadsheet that tracks different metrics throughout the year. Each column represents a month, and we keep adding new columns as time goes by.

I’m trying to figure out how to automatically get the latest value for each metric. Basically, I want a formula that always shows the number from the rightmost column in a given row. This way, it’ll update on its own whenever we add a new month.

Does anyone know a good way to do this? I’ve been scratching my head over it for a while now. Any help would be awesome!

Here’s a simple example of what my sheet looks like:

       Jan  Feb  Mar  Apr
Metric1  10   15   20   25
Metric2   5    8   12   18
Metric3  30   35   40   45

I want to get 25, 18, and 45 automatically, even when we add May, June, and so on. Thanks in advance!

2

I’ve encountered this issue before, and there’s a neat solution using the INDEX and COUNTA functions.

Here’s the formula that should work for you:

=INDEX(2:2, 1, COUNTA(2:2))

Replace ‘2:2’ with the appropriate row number. This formula counts the number of non-empty cells in the row and then returns the value in the last filled cell. It automatically adjusts as you add more columns, so long as there are no blank cells in between your data points.

Hope this helps solve your problem efficiently!

3

hey there, ClimbingLion! I’ve got a trick for ya. try using the LOOKUP function. it’s pretty nifty for this kinda thing. here’s how:

=LOOKUP(2,1/(2:2<>“”),2:2)

just swap out 2:2 with ur row number. this bad boy will grab the last non-empty value in the row. works like a charm when u keep addin new months!

4

As someone who’s been wrestling with spreadsheets for years, I’ve found a solution that might just do the trick for you. Have you considered using the OFFSET function combined with COUNTA? Here’s what I’ve used in similar situations:

=OFFSET(A2,0,COUNTA(2:2)-1,1,1)

This formula starts at cell A2 (adjust as needed for your sheet), then moves right by the count of non-empty cells in the row minus 1. It’s been a lifesaver for me when dealing with constantly expanding datasets.

One word of caution though - make sure you don’t have any empty cells in your data range, or it might throw things off. If you do, you might need to tweak the approach a bit. Let me know if you need any clarification on how to implement this!