Get the First or Last Non-Blank Value

Excel Formulas › Lookup

All versionsLOOKUPXLOOKUP

To grab the latest entry in a column — the most recent reading, the last filled cell — or the first one, you need a formula that skips blanks. The classic LOOKUP(2,1/…) trick nails the last value; XLOOKUP does it cleanly in modern Excel.

Quick formula: to return the last non-blank value in A2:A100:
=LOOKUP(2, 1/(A2:A100<>""), A2:A100)
Dividing 1 by “is it non-blank?” makes blanks into errors; LOOKUP skips errors and lands on the last real value.

Functions used (tap for the full reference guide):

LOOKUPXLOOKUPINDEX

The example

A reading column with gaps. We want the last reading.

AB
1Reading
212.1
3
412.8
5
6Last reading:12.8

The formula

The most recent reading:

=LOOKUP(2, 1/(A2:A5<>""), A2:A5) // skips the blanks → 12.8

How it works

The LOOKUP(2, 1/…) trick is the workhorse:

  1. A2:A5<>"" builds a TRUE/FALSE array of “has a value.”
  2. Dividing 1 by it gives 1 for filled cells and #DIV/0! for blanks.
  3. LOOKUP searches for 2 — which never exists — so it walks to the last non-error value, i.e. the last filled cell: 12.8.
  4. For the first non-blank instead, use INDEX/MATCH or XLOOKUP (see variations).

Modern and readable: last non-blank = =XLOOKUP(TRUE, A2:A100<>"", A2:A100, , 0, -1) (searches bottom-up). First non-blank = the same with search_mode 1.

Try it: interactive demo

Live demo

Type a list with blanks (empty commas); see the first and last non-blank.

First:   Last:

Variations

First non-blank (INDEX/MATCH)

Find the first cell that isn’t empty:

=INDEX(A2:A100, MATCH(TRUE, A2:A100<>"", 0))

Last non-blank with XLOOKUP

Search from the bottom up:

=XLOOKUP(TRUE, A2:A100<>"", A2:A100, , 0, -1)

Last number only

Ignore text cells too:

=LOOKUP(9.99E+307, A2:A100)

Pitfalls & errors

Formulas returning "" count as non-blank to <>""? No — <>"" treats an empty string as blank, which is usually what you want. Use ISBLANK logic if you need the strict difference.

The LOOKUP trick looks weird but is robust. Searching for 2 in an array of 1s and errors reliably returns the last 1.

XLOOKUP/array versions need 365 or CSE. The A2:A100<>"" array works natively in 365; older Excel may need Ctrl+Shift+Enter for the INDEX/MATCH form.

Practice workbook

📊
Download the free Get the First or Last Non-Blank Value practice workbook
A gappy column with the live last-non-blank (LOOKUP trick), the first-non-blank and XLOOKUP variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I get the last non-blank value in a column in Excel?
Use =LOOKUP(2, 1/(range<>""), range). Dividing 1 by the non-blank test makes blanks into errors, and LOOKUP returns the last non-error value. In Excel 365, =XLOOKUP(TRUE, range<>"", range, , 0, -1) is clearer.
How do I get the first non-blank value?
Use =INDEX(range, MATCH(TRUE, range<>"", 0)) or =XLOOKUP(TRUE, range<>"", range) which finds the first cell that isn't empty.
How does LOOKUP(2,1/(range<>"")) work?
The expression makes filled cells 1 and blanks #DIV/0!. Searching for 2, which never exists, forces LOOKUP to walk to the last 1, returning the last non-blank value.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Get the most recent match · Check if a cell is blank · Count blank cells

Function references: LOOKUP · XLOOKUP · INDEX