How to find the first non-blank value in Excel

XLOOKUP(TRUE, range<>"") in Excel 365

The sheet tracks monthly unit sales for snack products that launched at different times, so each row starts with gaps. B2:G2<>"" turns the Oat bars row into FALSE, FALSE, TRUE, TRUE, TRUE, TRUE. XLOOKUP looks for TRUE in that list and, like any XLOOKUP, stops at the first match from the left.

Write it in H2 and fill down to get every product's first figure. XLOOKUP is in Excel 365 and Excel 2021 and later. The same pattern works down a column: =XLOOKUP(TRUE,C2:C7<>"",A2:A7) returns the first product with anything in the Feb column, Protein balls.

Return the header: the first month with sales

Often the value is not what you want; you want to know when. Keep the same test and point the return range at the header row. The two ranges must be the same width, B to G in both.

For a missing row, use XLOOKUP's fourth argument. Granola cups has no sales at all, so a plain XLOOKUP returns #N/A, while =XLOOKUP(TRUE,B6:G6<>"",B1:G1,"Not launched") shows Not launched.

Older Excel: INDEX and MATCH(TRUE)

MATCH(TRUE,B2:G2<>"",0) finds the position of the first filled cell, 3 here. In Excel 2019 and older, a comparison over a range inside MATCH needs Ctrl+Shift+Enter. Wrapping it in INDEX(...,0) makes Excel handle the array itself, so a normal Enter works.

Swap the first INDEX range for B1:G1 to return the month, and wrap the whole thing in IFERROR for rows that are empty: =IFERROR(INDEX(B1:G1,MATCH(TRUE,INDEX(B6:G6<>"",0),0)),"Not launched").

First number only, or first non-zero

"Not blank" is not always what you mean. Protein balls has n/a typed in Jan and Feb, so the <>"" test returns n/a. Test for numbers with ISNUMBER and you get the first real sales figure, 95 in Mar.

Trail mix was listed in January with 0 sales. Its first non-blank value is that 0 (Jan). For the first month with real sales, test <>0 instead: =XLOOKUP(TRUE,B3:G3<>0,B1:G1) returns Apr, 125 units. An empty cell counts as 0 in this comparison, so blanks are skipped too.

Text breaks both shortcuts. On the Protein balls row, <>0 lands on n/a again (Jan), and so does >0, because Excel ranks text above every number when it compares. When a row can hold text, combine the tests: =XLOOKUP(1,ISNUMBER(B4:G4)*(B4:G4<>0),B1:G1) returns Mar.

Cells that look empty but are not

Seed crackers' Jan and Feb cells hold a formula that returns "", left over from a template. They look empty. The <>"" test treats them as blank and correctly returns 180 in Jun. ISBLANK does not: ISBLANK(B5) is FALSE because the cell has a formula, so =XLOOKUP(TRUE,NOT(ISBLANK(B5:G5)),B1:G1) wrongly says Jan. COUNTA counts them too, 3 cells for this row. Use <>"" rather than ISBLANK when formulas are involved.

A space is the other trap. The meter log in I:J has a space typed in the 3 Nov cell, so =XLOOKUP(TRUE,J2:J7<>"",I2:I7) returns 3 Nov, a day with no reading. Trim before testing: LEN(TRIM(...))>0 ignores cells that only hold spaces. For a column of readings, ISNUMBER(J2:J7) works just as well and gives the same 4 Nov.