Spreadsheet Practice

How to find the last non-blank value in a column in Excel

Formula (Excel and Google Sheets)

=XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1)

Search from the bottom for the first non-empty cell: =XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1).

Result on the example
42,861 in E1
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

How it works

B2:B10<>"" is TRUE for every cell that has a reading. XLOOKUP looks for TRUE in that list, and search mode -1 starts from the bottom, so the first TRUE it meets is the last reading, 42,861. The gap on March 23 doesn't matter.

=XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1)

Result42861

  1. B2:B109 rows × 1 col: 41,250, 41,538, 41,802, (empty), …

    A range: 9 cells in one column.

  2. B2:B10<>""{9 rows × 1 col} TRUE, TRUE, TRUE, FALSE, …

    The <> operator tests whether they differ.

  3. -1-1

    Makes the value negative.

  4. XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1)42861

    XLOOKUP: Searches one range and returns the matching item from another.

    • lookup_valueTRUE
    • lookup_arrayB2:B10<>""{9 rows × 1 col} TRUE, TRUE, TRUE, FALSE, …
    • return_arrayB2:B109 rows × 1 col: 41,250, 41,538, 41,802, (empty), …
    • if_not_found(omitted)
    • match_mode0
    • search_mode-1-1

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1). We ran it in Google Sheets mode on the same data and it returns 42,861.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=INDEX(B2:B10,MAX((B2:B10<>"")*(ROW(B2:B10)-ROW(B2)+1)))42,861Works in every Excel version (Ctrl+Shift+Enter in 2019 and earlier): MAX finds the position of the last filled cell.
=TAKE(FILTER(B2:B10,B2:B10<>""),-1)42,861Filter out the blanks and take the last item. Excel 2024 and Microsoft 365.

Common mistakes

  • =INDEX(B:B,COUNTA(B:B)) only works when there are no gaps. Here it would return a blank, because one week has no reading.
  • A cell holding a single space isn't blank. Use TRIM(B2:B10)<>"" if the data might contain stray spaces.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Find the last non-blank value in a column

The log has dates filled in ahead of time. In E1, return the most recent odometer reading: the last non-blank value in B2:B10.

Questions

How do I return the date of the last reading?

Change the return range: =XLOOKUP(TRUE,B2:B10<>"",A2:A10,,0,-1).

How do I get the last number and ignore text?

Test for numbers: =XLOOKUP(TRUE,ISNUMBER(B2:B10),B2:B10,,0,-1).

What about the older LOOKUP(2,1/…) trick?

=LOOKUP(2,1/(B2:B10<>""),B2:B10) does the same in any version. XLOOKUP is easier to read.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.