SheetSense

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.

fx
=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

  1. 01A2:A1000<>"" is TRUE for filled cells and FALSE for blanks.
  2. 021 divided by TRUE is 1 and 1 divided by FALSE is an error, so only filled rows produce a number.
  3. 03LOOKUP(2,...) searches for 2, which is larger than any 1, so it matches the last number in the array.
  4. 04It returns the value from A2:A1000 at that position.

The same logic in four tools

Excel
=LOOKUP(2,1/(A2:A1000<>""),A2:A1000)
Google Sheets
=ARRAYFORMULA(LOOKUP(2,1/(A2:A1000<>""),A2:A1000))
SQL
-- 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;
Python (pandas)
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.