Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to return the first non-blank value in Excel and Google Sheets
Heads up: Excel and Google Sheets do this differently.
Excel
=INDEX(B2:F2,MATCH(TRUE,B2:F2<>"",0))Google Sheets
=COALESCE(B2,C2,D2,E2,F2)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In Excel, enter =INDEX(B2:F2,MATCH(TRUE,B2:F2<>"",0)) as a Ctrl+Shift+Enter array formula (or just Enter in Excel 365 with dynamic arrays).
- 2In Google Sheets, use the simpler =COALESCE(B2,C2,D2,E2,F2) — it returns the first non-null, non-empty argument.
- 3Adjust the range to cover all columns (or rows) you want to search through.
Tips
- In Excel 365 you can also use =XLOOKUP("",B2:F2,B2:F2,"",0,-1) — set match_mode to 0 (exact) and search_mode to -1 (last-to-first) to get the last non-blank instead; reverse for first.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Does COALESCE work in Excel?
No — COALESCE is a Google Sheets function. Use INDEX/MATCH or IFERROR chains in Excel.
What if the first non-blank is a number?
Both approaches return the value as-is, whether text or numeric, so no adjustment is needed.