Search code examples
google-sheetsgoogle-sheets-formula

How do I find the last non-empty cell in a range (accounting for both row and column)?

I have a weight tracker where I log my weight everyday. I am trying to figure out a formula using INDEX, COUNT and other possible functions to get the content of the last entered cell but unable to find the right syntax/combination. The last non-empty cell can be in any row or column.

I know how the function to find just based on the row or just the column. But how do I factor in both at the same time?

Attached is a sample sheet.[Weight Tracker]https://docs.google.com/spreadsheets/d/e/2PACX-1vRHsRZxGoF_INF2bUawy_yIKweH24bnG4m7_XUXOWe2NUr0PoRAOY-P7TQT4Nmy3461IGqmdkSHWO0w/pubhtml

The formula I have tried using is =INDEX(B2:H5,COUNTA(B2:B),COUNTA(B:H))

I want the Cell J2 to show my most recent entered value (182.7). The next day, when I enter a new value in the next column (Thursday), I'd like J2 to be updated dynamically to the new value. Next week, I'd move to the next row and so on and so forth.

Thanks in advance for any help!

Solution

  • You may try:

    =chooserows(tocol(B2:H,1),-1)

    OR

    =lookup(9^9,tocol(B2:H,1)) **numbers-exclusive & not intended for alphabets/mixed-data types

    enter image description here