How to find the first non-blank value in a row in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,B2:F2<>"",B2:F2)Look for the first TRUE in a "not empty" test: =XLOOKUP(TRUE,B2:F2<>"",B2:F2), filled down.
- Result on the example
- one result per row in
G2, filled down toG6 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2, filled down to G6. Change any input and the result updates, or edit the formula to experiment.
How it works
B2:F2<>"" turns the row into TRUE and FALSE values, one per month. XLOOKUP finds the first TRUE from the left and returns the sales in the same position: Boise's first sales are in March, 9,800.
=XLOOKUP(TRUE,B2:F2<>"",B2:F2)Result18400
B2:F21 row × 5 cols: $18,400, $21,950, $23,100, $24,800, …A range: 5 cells in one row.
B2:F2<>""{1 row × 5 cols} TRUE, TRUE, TRUE, TRUE, …The <> operator tests whether they differ.
XLOOKUP(TRUE,B2:F2<>"",B2:F2)18400XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
B2:F2<>""{1 row × 5 cols} TRUE, TRUE, TRUE, TRUE, … - return_array
B2:F21 row × 5 cols: $18,400, $21,950, $23,100, $24,800, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,B2:F2<>"",B2:F2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(B2:F2,MATCH(TRUE,INDEX(B2:F2<>"",0),0)) | $18,400, $9,800, $15,200… | Works in every Excel version without Ctrl+Shift+Enter: the inner INDEX(…,0) makes the test work as an array. |
Common mistakes
- A formula that returns "" counts as blank in this test, which is usually what you want. A cell with a space does not.
- Zeros are not blank. To skip zeros too, test
(B2:F2<>"")*(B2:F2<>0)and look for 1.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the first non-blank value in a row
G2, return each store's first month of sales (the first filled cell in B2:F2), then fill down to G6.Questions
How do I return the month instead of the value?
Return the header row: =XLOOKUP(TRUE,B2:F2<>"",B$1:F$1).
How do I find the first non-blank value in a column?
The same formula on a column range: =XLOOKUP(TRUE,A2:A20<>"",A2:A20).
How do I return the first of several phone columns that is filled?
That is the same pattern: =XLOOKUP(TRUE,B2:D2<>"",B2:D2,"No number") picks mobile, then work, then home.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.