Find last populated entry in row
I'm using libre office calc but im hoping the formula will be the same. I simply want to find the last populated value in a row and display it elsewhere. The row is left to right and may have empty values.
I've tried other answers and tried RTFM but i don't think I am searching the correct thing.
To get the column index of the last populated cell, try the following array formula (arbitrarily assuming your data lives in row A1:ZZ1):
=MAX(IF(A1:ZZ1<>"";COLUMN(A1:ZZ1)))
The following array formula does the same, just with an additional translation of the column index to the column's name:
=SUBSTITUTE(ADDRESS(1;MAX(IF(A1:ZZ1<>"";COLUMN(A1:ZZ1)));4);"1";"")
This is a combination of the following sources (Credits...):
- Dirk Reichel's answer on Find Last Row Cell in Spreadsheets (CALC, excel) with formula, based on the last entry (text / number)
- John Sun's post on OpenOfficeForums Thread "Derive Column Letter from Formula"
EDIT
To dynamically get the value of the last populated cell in Row 1, just embed the first formula into a INDIRECT(ADDRESS(1, <formula>)) clause. ADDRESS(1, <formula>) creates a cell reference, INDIRECT() gets the value based on that cell reference. So, the complete (first) formula would look like this:
=INDIRECT(ADDRESS(1;MAX(IF(A1:ZZ1<>"";COLUMN(A1:ZZ1)))))
Complete example:
