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

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

**Excel:** `=XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Date | Odometer |  | Latest reading |
| 2026-03-02 | 41250 |  |  |
| 2026-03-09 | 41538 |  |  |
| 2026-03-16 | 41802 |  |  |
| 2026-03-23 |  |  |  |
| 2026-03-30 | 42315 |  |  |
| 2026-04-06 | 42577 |  |  |
| 2026-04-13 | 42861 |  |  |
| 2026-04-20 |  |  |  |
| 2026-04-27 |  |  |  |

Formula in E1: `=XLOOKUP(TRUE,B2:B10<>"",B2:B10,,0,-1)`

Result: 42,861

## 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.

## Other ways

- `=INDEX(B2:B10,MAX((B2:B10<>"")*(ROW(B2:B10)-ROW(B2)+1)))`: Works 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)`: Filter 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/last-non-blank-value-in-column