Find the First Non-Blank Cell in a Row or Column in Excel

To find the first non-blank cell in a column or row in Excel, test every cell for "not empty" and look up the first TRUE. In Microsoft 365 that is one short formula, =XLOOKUP(TRUE,B2:B8<>"",B2:B8), and older versions use INDEX and MATCH in the same way. This guide is for anyone who needs the first reading, first sale or first contact date from a list with gaps.

The example data#

All the column examples use these weekly meter readings in A1:B8. Readings started in week 3, and week 5 was missed:

The goal is to return 1,520, the first reading, and 3, the week it was taken.

How the "not blank" test works#

The trick behind every formula in this guide is the comparison B2:B8<>"". The <> sign means "not equal to", and "" means empty text. So Excel checks each cell and returns a list of TRUE and FALSE values:

The first TRUE is in the third position. Any lookup function that searches this list for TRUE finds that position, and you can then return whatever you like from the same row. The lesson on IS functions and SWITCH covers other tests, such as ISNUMBER, that also produce TRUE and FALSE lists.

The quick way: XLOOKUP#

XLOOKUP can search the TRUE/FALSE list directly. Look up TRUE, in the test, and return the readings:

=XLOOKUP(TRUE,B2:B8<>"",B2:B8)

The result is 1,520. To get the week instead, change only the last range:

=XLOOKUP(TRUE,B2:B8<>"",A2:A8)

That returns 3. If you want the position within the range rather than a value, XMATCH does the same search: =XMATCH(TRUE,B2:B8<>"") also returns 3, because B4 is the third cell of B2:B8.

If every cell might be empty, add XLOOKUP's fourth argument so you get a message instead of #N/A:

=XLOOKUP(TRUE,B2:B8<>"",B2:B8,"No readings")

XLOOKUP and XMATCH need Microsoft 365, Excel 2021 or later, or Excel for the web. The XLOOKUP function guide and the full XLOOKUP tutorial explain the other arguments.

Older Excel: INDEX and MATCH#

In Excel 2019 and earlier, use MATCH to find the position of the first TRUE and INDEX to return the value at that position:

=INDEX(B2:B8,MATCH(TRUE,INDEX(B2:B8<>"",0),0))

Read it from the inside out:

1. INDEX(B2:B8<>"",0) builds the TRUE/FALSE list. Wrapping the test in INDEX with 0 lets older Excel handle the list without pressing Ctrl+Shift+Enter.
2. MATCH(TRUE, ..., 0) finds the first exact TRUE: position 3.
3. INDEX(B2:B8, 3) returns the third cell: 1,520.

To return the week instead, change the first range to A2:A8. In Microsoft 365 the simpler =INDEX(B2:B8,MATCH(TRUE,B2:B8<>"",0)) works too, because it handles arrays natively. The guides to INDEX and MATCH, and the INDEX MATCH tutorial, explain how the pair works.

First non-blank cell in a row#

The same formulas work across a row: just use row ranges. This table shows monthly purchases, with gaps before each customer's first order:

To find each customer's first order amount, put this in F2:

=XLOOKUP(TRUE,B2:E2<>"",B2:E2)

To find the month of their first order, return the header row instead:

=XLOOKUP(TRUE,B2:E2<>"",B$1:E$1)

Fill both down (Windows: Ctrl+D; Mac: ⌘D). Alba's first month is Mar with 300, Bruno's is Jan with 120, and Chidi's is Feb with 410. The $ in B$1:E$1 keeps the formula pointing at the header row as it moves down.

First number, first text or first non-zero#

Change the test to find a specific kind of value. Each of these looks up TRUE:

ISNUMBER is useful when a column mixes numbers with notes such as "n/a": it skips the notes and returns the first real number. The guide to Excel IS functions covers ISNUMBER, ISTEXT and ISBLANK in detail.

When a cell looks blank but isn't#

The <>"" test treats a cell as blank only if it is truly empty or holds a formula that returns "". Two cases need care:

• A cell with a space is not blank. If someone cleared a cell by typing a space, the formula returns that space. Use TRIM(B2:B8)<>"" instead, which removes spaces before testing.
• ISBLANK behaves differently. NOT(ISBLANK(B2:B8)) treats a formula that returns "" as filled, because the cell contains a formula. For "first visible value", <>"" is usually what you want.

The guide to checking if a cell is not blank explains these differences with more examples.

To get the last non-blank cell instead of the first, tell XLOOKUP to search from the bottom with a search mode of -1: =XLOOKUP(TRUE,B2:B8<>"",B2:B8,,0,-1) returns 1,610. The guide on looking up the last match covers that in full.

Why does the formula return #N/A or the wrong cell?#

Most problems come from one of four small slips:

1. Every cell is empty. There is no TRUE to find, so you get #N/A. Add XLOOKUP's "if not found" argument, or wrap the INDEX MATCH version in IFNA.
2. The ranges are different sizes. =XLOOKUP(TRUE,B2:B8<>"",A2:A9) returns #VALUE!. Make both ranges start and end on the same rows.
3. MATCH has no 0 at the end. Without the 0, MATCH does an approximate match on a list that isn't sorted, and the answer can be wrong without any error.
4. TRUE is in quotes. "TRUE" is text, not the value TRUE, so nothing matches. Type TRUE without quotes.

Practise it#

Try the exercise on this page: it returns the first meter reading with XLOOKUP. Then practise XLOOKUP in a live spreadsheet, where every formula you type is checked instantly. If you work in older versions of Excel, the INDEX and MATCH lesson builds the same skills step by step.