Lookup & Reference

Excel formula for Find the first non-blank value

Return the first cell in a range that actually contains data.

Copy this formula

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

How the formula works

A2:A100<>"" builds a TRUE/FALSE array of which cells are filled; XLOOKUP finds the first TRUE and returns the matching value. Handy when leading rows are blank or staggered.

Examples

=XLOOKUP(TRUE, B2:B50<>"", B2:B50)→First entry

Skips blanks to the first value.

Other ways to do this

Legacy (INDEX/MATCH)

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

Enter as an array in pre-365 Excel.

FAQ

How do I get the last non-blank value?

Add search mode -1: =XLOOKUP(TRUE, A2:A100<>"", A2:A100, , 0, -1).

Related formulas

Flag or handle blank cells Filter rows by criteria with a formula

Functions used

XLOOKUP INDEX MATCH

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free