Why does my left join return more rows than XLOOKUP?

The key appears more than once in the right-hand table. XLOOKUP returns only the first match; a join returns a row for each.

Is XLOOKUP the same as a left join?

Only when the right-hand key is unique and clean. Then both keep every left row and add the matching value.

Why does XLOOKUP return 0 instead of blank?

It returns the matched cell's value, and Excel shows a reference to an empty cell as 0. Add &"" to return an empty string instead.

XLOOKUP for a quick column inside one workbook with a unique key. A join when you need every match, repeatable steps, or larger files.

Left join vs XLOOKUP: when and why the results differ

By the Analistable team · Updated 2026-10-10 · 5 min read

XLOOKUP returns one value per row of your table — the first match — so the row count never changes. A left join returns every matching row, so a key that appears three times on the right gives three rows. They also differ on blanks (XLOOKUP shows 0, a join gives null) and on case (Power Query merges are case-sensitive, XLOOKUP isn't). Decide whether you want first match, all matches or an aggregate.

Part of our guide: How to join spreadsheets on a common column

Same idea, different contract

A left join and a column of XLOOKUPs both start from a left-hand table and bring in data from a right-hand one. On clean, one-to-one data they give identical results. The differences appear when the data isn't clean, and they're differences in design rather than bugs.

How the two behave
Situation	XLOOKUP column	Left join (SQL, Power Query, pandas)
Key appears once on the right	That value	That row
Key appears several times on the right	First match only	One row per match
Key missing on the right	#N/A or the if_not_found value	Row kept, nulls
Matched cell on the right is blank	0 (or blank text if forced)	null
Case differs (abc vs ABC)	Matches	Power Query and DuckDB: no match
Row count of the result	Always equals the left table	Equal or more

Worked example: more rows from the join

Accounts (left)
Account	Plan
A1	Pro
A2	Basic
A3	Pro

Contacts (right) — A1 has two contacts, A3 none
Account	Contact
A1	ana@a1.io
A1	ben@a1.io
A2	cy@a2.co

Results side by side
	XLOOKUP column	Left join
Rows	3	4
A1	ana@a1.io	ana@a1.io; ben@a1.io (2 rows)
A2	cy@a2.co	cy@a2.co
A3	#N/A	null
Count of Pro accounts	2	3 (A1 counted twice)

Neither is wrong. XLOOKUP silently dropped Ben; the join kept him but now counts account A1 twice if you count rows. That last line is where reports diverge: a COUNT or SUM over the joined table inflates anything that belongs to the left side. See duplicate rows after joining two tables for diagnosing it.

Which behaviour do you want?

One value per row, and you know the key is unique — either works. XLOOKUP is quicker inside a workbook; a join scales better and is easier to repeat.
One value per row, but the key repeats — decide which one. The latest? The largest? Make it explicit: sort and use XLOOKUP with search_mode, or in SQL aggregate or rank before joining. Relying on whichever row happens to be first is the hidden risk with XLOOKUP.
Every match — use a join, or FILTER in Excel/Sheets. XLOOKUP can't return several rows.
A summary per left row — aggregate the right side first (count of contacts, total of orders), then join or use SUMIFS/COUNTIFS. This gives one row per left row and keeps all the information.

SELECT a.account, a.plan,
       COUNT(c.contact) AS contacts,
       string_agg(c.contact, '; ') AS contact_list
FROM accounts a
LEFT JOIN contacts c USING (account)
GROUP BY a.account, a.plan;

This returns 3 rows — one per account — with A1 showing 2 contacts and both addresses, A3 showing 0. string_agg is DuckDB/PostgreSQL; other databases call it GROUP_CONCAT or STRING_AGG with slightly different syntax. In Excel the closest equivalent is TEXTJOIN with FILTER.

What Power Query does: Merge, then Expand

In Power Query a Merge Queries step with Left Outer adds a column of nested tables — one small table per left row, holding every matching right row. Nothing has multiplied yet. The fan-out happens when you click the expand button on that column: each nested row becomes its own row, exactly like a SQL left join.

That gives you a choice XLOOKUP doesn't. Instead of Expand, use Aggregate from the same button to return a count, sum, minimum or maximum per left row, which keeps the row count fixed. Or add a custom column with Table.First([Contacts]) to take the first match deliberately, after sorting the right-hand query so the first one is the one you want. All three are explained alongside the join kinds in Merge Queries join kinds.

The other differences: blanks, case and types

Blanks. When XLOOKUP's match points to an empty cell it returns 0, which looks like a real zero in a numeric column. A join returns null, which most tools leave out of averages. If you compare an average built from each, they'll differ. In XLOOKUP, append &"" to return blank text instead, or test with ISBLANK.

Case. XLOOKUP ignores case. A Power Query merge and SQL in DuckDB or PostgreSQL don't, so "Acme" and "ACME" match in Excel but not in the join. Normalise case on both sides if that's noise — see case-sensitive matching between two lists.

Types. Both fail on number-versus-text keys, but Power Query fails silently with nulls while XLOOKUP shows #N/A, which is easier to notice.

Making them agree

Check the right-hand key is unique (COUNTIF or GROUP BY … HAVING COUNT(*) > 1).
Clean keys identically on both sides: trim, case, type.
If the key repeats, choose first, last or aggregate explicitly in both methods.
Compare row counts and one control total between the two outputs.

Analistable answers cross-sheet questions with SQL joins in DuckDB inside your browser, and shows the query. If an answer differs from your XLOOKUP sheet, the usual reason is one of the above, and you can ask it to count keys per sheet to find which. For Power Query's join kinds, see Merge Queries join kinds; for lookup functions, VLOOKUP vs XLOOKUP vs INDEX MATCH; more in the join spreadsheets guide.

Frequently asked questions

Why does my left join return more rows than XLOOKUP?: The key appears more than once in the right-hand table. XLOOKUP returns only the first match; a join returns a row for each.
Is XLOOKUP the same as a left join?: Only when the right-hand key is unique and clean. Then both keep every left row and add the matching value.
Why does XLOOKUP return 0 instead of blank?: It returns the matched cell's value, and Excel shows a reference to an empty cell as 0. Add &"" to return an empty string instead.
Which should I use?: XLOOKUP for a quick column inside one workbook with a unique key. A join when you need every match, repeatable steps, or larger files.