CODEWITHSUNDEEP
google-sheets
yi1
yi1

Reputation: 413

Google Sheets - get the right-most value of a row

I have a sheet where the columns are months in a year and the rows are various metrics. Every month, we add another column on the right.

I need something that I can give a ROW and it will always return the right most value. That is, it automatically updates whenever we add a column for a new month.

Upvotes: 3

Views: 9885

Answers (3)

Dave F
Dave F

Reputation: 161

Adding this one for future readers. The formula I found years ago for obtaining the rightmost value was:

=index(2:2,1,COUNT(2:2))

However for each blank cells in amongst the cells with data, the returned value is the Nth last value (2 blank cells in row 2 and the formula will return the 3rd last value from the right, not the rightmost value). It appears to work, but won't be accurate in all cases.

As such, I do not recommend this formula as you can not depend on it if ever there will be an empty cell before the right-most within your data.

Upvotes: 0

yago
yago

Reputation: 178

I was very happy to have found @AdamL's answer and it did make my day, but I have since found a simpler way that works fine for my data sample, and that is using the LOOKUP function.

The LOOKUP function will look for a certain value in a given range, but if you pass it a humongous value, a value that is over your data range, it returns the last, rightmost value by default.

The answer is then very simple, just pass it the range - or row if that's what you need - and a huge value (many people do this using the biggest number that Excel can handle, but Google sheets is not Excel, and since I don't know what is the biggest number Google sheets can handle, I'll just give it a value well outside of my data set). Assuming you need to lookup into the entire row number 2:

=LOOKUP(999999999,2:2)

And that's it.

This function will throw an error if there isn't any data, so if you (like me) need to get that particular value only if it exists, you can combine this with a simple IF function:

=IF(ISERROR(LOOKUP(999999999,2:2)),"EMPTY",LOOKUP(999999999,2:2))

You can replace the string "EMPTY" with any value or function you want in there if the LOOKUP function returns an error.

I hope this simpler method is of any help, and thanks again to @AdamL for his original answer.

Upvotes: 7

AdamL
AdamL

Reputation: 24619

There's a few ways of doing it, but one way (considering row 2 in this example):

=FILTER(2:2,COLUMN(2:2)=MAX(FILTER(COLUMN(2:2),LEN(2:2))))

Upvotes: 4

Related Questions