Get the last value in a column
Dividing 1 by a TRUE or FALSE test makes errors for blank rows, and LOOKUP with 2 skips the errors and lands on the last filled cell.
=LOOKUP(2,1/(A2:A1000<>""),A2:A1000)The sheet: Column A is a log that grows each week with a reading in each row. You want the most recent reading, which is the last non-empty cell.
How it works, step by step
- 01A2:A1000<>"" is TRUE for filled cells and FALSE for blanks.
- 021 divided by TRUE is 1 and 1 divided by FALSE is an error, so only filled rows produce a number.
- 03LOOKUP(2,...) searches for 2, which is larger than any 1, so it matches the last number in the array.
- 04It returns the value from A2:A1000 at that position.
The same logic in four tools
=LOOKUP(2,1/(A2:A1000<>""),A2:A1000)=ARRAYFORMULA(LOOKUP(2,1/(A2:A1000<>""),A2:A1000))-- Requires a column that defines row order, such as logged_at.
SELECT reading
FROM readings
WHERE reading IS NOT NULL
ORDER BY logged_at DESC
LIMIT 1;last_value = df["reading"].dropna().iloc[-1]Mistakes that break it
- !It returns the last filled cell by position, not the largest or latest by date. Sort first if order matters.
- !A cell containing a formula that returns "" is treated as empty by this test.
- !Make the range large enough for future rows, as the formula does not extend itself.
- !SQL tables have no inherent row order, so you must choose an ordering column.
Common questions
- Is there a simpler way in Microsoft 365?
- Yes. =TAKE(FILTER(A2:A1000,A2:A1000<>""),-1) returns the last non-blank value.
- How do I get the last number only?
- Use =LOOKUP(9.99E+307,A2:A1000), which finds the last number and ignores text.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.