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

Solution 1:

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))))

Solution 2:

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.