Excel COALESCE Function: How to Return the First Non-Blank Value

Microsoft Excel does not have a built-in worksheet COALESCE function the way SQL, DAX, and Power Query do. To get the same result on a worksheet — return the first value in a list that isn’t blank — use IFS (Excel 2019/2021/365), a nested IF, or an INDEX/MATCH formula. If you only need to skip errors rather than blanks, use IFERROR. And if your data lives in Power Query or the Data Model, you get a real COALESCE there.

This guide shows every reliable way to reproduce COALESCE in Excel, when to use each one, and the traps that trip people up (empty text "" is not the same as a truly blank cell).

What COALESCE means — and why Excel doesn’t have it

In SQL, COALESCE(a, b, c) returns the first argument that isn’t NULL. People want the same thing in Excel: scan a few columns (a primary value, a fallback, a default) and pull the first one that actually contains something. Excel never shipped a worksheet function with that name, so the job falls to a combination of functions you already have.

Two things make “first non-blank” subtler than it looks:

• A cell can look empty but hold an empty string "" returned by a formula. ISBLANK treats that cell as not blank, while a comparison like <>"" treats it as blank. Pick your test deliberately.
• If every candidate is empty, most of these formulas return an error or an empty result — so you usually wrap the whole thing in IFERROR to supply a clean default.

Method 1 — IFS (the closest modern equivalent)

If you have Excel 2019, 2021, or Microsoft 365, the IFS function is the cleanest stand-in for COALESCE across a fixed set of cells:

=IFS(A2<>"", A2, B2<>"", B2, C2<>"", C2, TRUE, "Not found")

IFS checks each condition left to right and returns the value paired with the first TRUE. Because the test is <>"", both truly blank cells and formula-produced empty strings are skipped. The final TRUE, "Not found" is your default when everything is empty — it replaces the need for a separate IFERROR.

Method 2 — Nested IF (works in every version)

On older Excel, reproduce the same logic with nested IF functions:

=IF(A2<>"", A2, IF(B2<>"", B2, IF(C2<>"", C2, "Not found")))

It reads the same way IFS does — first non-empty wins — and runs on any version of Excel, Windows or Mac. Beyond three or four levels it gets hard to read, which is exactly why IFS exists.

Method 3 — INDEX/MATCH across a range

When your candidates sit in a contiguous range (say A2:E2) and you don’t want to spell out each cell, use INDEX with MATCH:

=INDEX(A2:E2, MATCH(TRUE, A2:E2<>"", 0))

A2:E2<>"" builds an array of TRUE/FALSE, and MATCH finds the position of the first TRUE (the first non-blank). In Microsoft 365 and Excel 2021 this just works. In Excel 2019 and earlier you must confirm it with Ctrl+Shift+Enter (Control+Shift+Return on Mac) so it evaluates as an array formula.

If you specifically want to ignore truly blank cells but keep empty strings, swap the test for ISBLANK:

=INDEX(A2:E2, MATCH(FALSE, ISBLANK(A2:E2), 0))

Remember the difference: <>"" treats a formula’s "" as blank; ISBLANK does not. Use whichever matches how your data was produced.

Method 4 — IFERROR to coalesce away errors

COALESCE is sometimes really about skipping errors — a lookup that might fail, then a backup lookup. Chain IFERROR:

=IFERROR(VLOOKUP(A2, Table1, 2, 0), IFERROR(VLOOKUP(A2, Table2, 2, 0), "Not found"))

The first VLOOKUP runs; if it returns #N/A, the next one runs; if that also fails, you get the default. This is the right tool when “missing” means an error, not a blank. (For why lookups throw #N/A and how to tame error values generally, see fixing common Excel errors.)

Method 5 — TEXTJOIN to merge instead of pick

If you don’t want the first non-blank value but all of them combined, TEXTJOIN is the move:

=TEXTJOIN(" ", TRUE, A2:E2)

The second argument, TRUE, tells TEXTJOIN to ignore empty cells, so you get a clean, space-separated string of only the populated values. It’s a common step when cleaning up messy data spread across several columns.

Method 6 — Real COALESCE in Power Query and DAX

The Excel ecosystem does have COALESCE — just not on the worksheet grid:

• Power Query (Get & Transform): the M language has a coalesce operator, ??. A custom column formula like [Phone] ?? [Mobile] ?? "n/a" returns the first non-null value. This is ideal when you’re already shaping data in Power Query before it lands in a table.
• Power Pivot / Data Model (DAX): there’s a genuine COALESCE function — = COALESCE(Sales[Discount], 0) — for measures and calculated columns.

If you regularly need first-non-null logic over large datasets, doing it in Power Query or DAX is cleaner and faster than worksheet formulas.

Which method should you use?

Method	Returns	Versions	Best for
IFS	First non-empty of listed cells	2019/2021/365	A handful of specific columns
Nested IF	First non-empty of listed cells	All versions	Same, on older Excel
INDEX/MATCH	First non-blank in a range	All (CSE pre-2021)	Many candidates in one range
IFERROR chain	First value that isn’t an error	All versions	Fallback lookups
TEXTJOIN	All non-blank values, joined	2019/2021/365	Combining, not choosing
Power Query ?? / DAX COALESCE	First non-null	365/2021 + Data Model	Large or repeatable data jobs

Troubleshooting

• You get #N/A. Every candidate was empty, so MATCH found nothing. Wrap the formula in IFERROR(..., "Not found"), or use the TRUE, default clause in IFS.
• A “blank-looking” cell is being picked. It probably contains "" from a formula. Switch your test between <>"" and ISBLANK depending on whether you want those treated as blank. See how ISBLANK behaves and how to count truly blank cells.
• The INDEX/MATCH array formula returns the wrong cell or an error in older Excel. Re-enter it with Ctrl+Shift+Enter (Control+Shift+Return on Mac).
• You wanted unique values, not the first one. That’s a different job — see finding unique values and the FILTER function.

Frequently Asked Questions

Is there a COALESCE function in Excel?

Not on the worksheet. Excel has no native =COALESCE() cell function. You reproduce it with IFS, nested IF, or INDEX/MATCH. A real COALESCE does exist in Power Query (the ?? operator) and in DAX for the Power Pivot Data Model.

How do I return the first non-blank cell in a row or column?

Use =INDEX(range, MATCH(TRUE, range<>"", 0)) for a contiguous range, or =IFS(A2<>"",A2, B2<>"",B2, TRUE,"") for specific cells. In Excel 2019 or earlier, enter the INDEX/MATCH version with Ctrl+Shift+Enter.

What’s the difference between using <>"" and ISBLANK?

<>"" counts a formula’s empty-string result as blank, so it gets skipped. ISBLANK returns FALSE for that same cell because it technically contains a value. Choose the test that matches whether your “empty” cells are truly empty or formula-produced.

How do I COALESCE to skip errors instead of blanks?

Chain IFERROR: =IFERROR(first, IFERROR(second, default)). Each IFERROR hands off to the next argument only when its formula returns an error such as #N/A.

Does COALESCE work in Power Query?

Yes. Power Query’s M language uses the ?? coalesce operator — for example [A] ?? [B] ?? "n/a" returns the first non-null value. It’s the most efficient option when you’re already transforming data in Power Query.

How do I give a default value when everything is blank?

Add a final fallback: the TRUE, "default" clause in IFS, the last argument of a nested IF, or IFERROR(formula, "default") wrapped around an INDEX/MATCH. For more on building robust formulas, see the complete guide to Excel formulas and Excel logical functions.