How to Get the First Non-Blank Value Across Columns in Excel
Three columns hold a phone number — Mobile, Work, Home — and you need one. Take the mobile if there is one, the work number if there isn't, the home number as a last resort. SQL calls this COALESCE. Excel has no such function, so here is how to do it anyway.
Last updated: October 2026
| Mobile | Work | Home | Phone | |
|---|---|---|---|---|
| 020 7946 | 020 8123 | → | 020 7946 | |
| 07700 900 | → | 07700 900 | ||
| → |
Method 1 — a formula
- In Microsoft 365:
=INDEX(A2:C2,MATCH(TRUE,INDEX(A2:C2<>"",0),0)). - It returns
#N/Awhen the whole row is blank, so in practice you wrap it:=IFERROR(…,""). - It also fixes the priority to left-to-right. Wanting Work checked before Mobile means rewriting the range, or moving columns around.
- And it tells you nothing about which column supplied the answer.
Method 2 — the 1-click way (Tellsheet)
Coalesce Columns
Select your data and run Coalesce Columns. It adds one column holding the first value that is actually there, reading the columns in whatever priority order you give (2,1,3 checks the second column first). Optionally it adds a second column naming where each value came from. A zero counts as a value — only empty cells and cells holding nothing but spaces fall through. Different from Fill Blanks, which fills downwards from the row above, and from Merge Cells, which joins everything together.
Where this comes up
- Contact records — mobile, then work, then home into one Phone column.
- Addresses from several systems — the CRM address if present, else the billing address.
- Imports with optional fields — a preferred name column that is usually empty, falling back to the legal name.
- Readings and measurements — the manual reading where there is one, else the automatic one.
Frequently asked questions
Does Excel have COALESCE?
No — nested IFs or an INDEX/MATCH array formula are the usual stand-ins. Coalesce Columns is the one-click version.
Is a zero treated as blank?
No. 0 is a real value and wins; only empty or whitespace-only cells fall through.
Can the priority differ from left to right?
Yes — give the columns in the order you want them checked.
Can I tell which column was used?
Yes, with the optional source column.
Related Excel guides
One column, from whichever one has the value
Coalesce Columns collapses any number of columns into one, in your priority order — with a preview before anything changes.
Get Tellsheet free See pricing