XLOOKUP returns the wrong row after merging — how to find and fix silent mismatches
By the Analistable team · Updated · 5 min read
A wrong value is worse than #N/A because nothing flags it. The usual causes are duplicate keys (XLOOKUP returns the first match), a match_mode of -1 or 1 returning the nearest value, lookup and return ranges that start on different rows, and wildcard characters with match_mode 2. Check each result by also returning the key it matched and counting how often each key appears.
Part of our guide: How to join spreadsheets on a common column
Why wrong matches are silent
XLOOKUP's job is to return something whenever it can. If the key appears twice, it gives you the first one. If you asked for the next smaller item, it gives you a neighbour. If the return range is shifted by a row, it gives you the next record's value. None of those raise an error, so a merged sheet can look complete while some rows carry another record's data.
Each cause below has a test you can run on the merged sheet. Running all of them takes a few minutes and is worth it before anyone uses the numbers.
Cause 1: duplicate keys return the first hit
XLOOKUP searches from the top by default and stops at the first match. If a price list has SKU A-17 twice — an old price and a new one — every lookup gets whichever is higher up the sheet.
| Row | SKU | Price | Valid from |
|---|---|---|---|
| 2 | A-17 | 4.5 | 2025-01-01 |
| 3 | B-02 | 9 | 2025-01-01 |
| 4 | A-17 | 4.95 | 2026-04-01 |
=XLOOKUP("A-17", B2:B4, C2:C4) returns 4.50, the out-of-date price. Count keys first with =COUNTIF(B:B, B2) and filter for anything above 1. Then decide on a rule: remove the stale row, sort so the right row comes first, search from the bottom with search_mode -1 (=XLOOKUP(F2, B:B, C:C, , 0, -1)), or add the extra key column — here the valid-from date — that makes rows unique. In a join the same duplicates multiply rows instead; see duplicate rows after joining two tables.
Cause 2: approximate match mode
XLOOKUP's fifth argument, match_mode, defaults to 0 (exact). With -1 it returns the exact match or the next smaller item; with 1, the next larger. Those are right for tax bands and price tiers and wrong for IDs: look up invoice 10452 in a list missing it and you get 10451's customer.
VLOOKUP is more dangerous because approximate match is its default. =VLOOKUP(F2, A:C, 3) with no fourth argument is an approximate lookup that expects sorted data. Use Formulas → Show Formulas and scan a merged workbook for VLOOKUPs without FALSE or 0 as the last argument.
Cause 3: lookup and return ranges don't line up
XLOOKUP checks that the two arrays are the same size, not that they start on the same row. =XLOOKUP(F2, Prices!A2:A500, Prices!C3:C501) is valid and returns the price one row below the match for every lookup. This happens after hand-editing a range, or after inserting a header row on one sheet when the formula points to a different sheet.
Similar misalignment happens when someone sorts one column of the source instead of the whole table — the keys move and the values don't. The fix is structural: convert the source to an Excel Table (Ctrl+T) and use column references such as Prices[SKU] and Prices[Price], which always align and resize together.
Cause 4: wildcards and partial matches
With match_mode 2, XLOOKUP treats * ? and ~ as wildcards, so a key like PRO* matches the first SKU starting with PRO. VLOOKUP, MATCH and XMATCH's wildcard mode behave the same way, and VLOOKUP and MATCH use wildcards even in exact mode. Keys from free text — product names, references with asterisks — are the risk. Use XLOOKUP with match_mode 0, or escape with ~*.
How to verify every match
Add check columns next to the merged values, then filter for problems.
- Return the key too.
=XLOOKUP(F2, Prices[SKU], Prices[SKU])should give back exactly F2. With match_mode -1/1 or wildcards it won't. Compare with=EXACT(F2, G2). - Count matches.
=COUNTIF(Prices[SKU], F2)should be 1. Anything higher means the first match was chosen for you. - Cross-check a second field. If both sheets have a name or date, look that up too and compare; a mismatch exposes a wrong row.
- Control totals. Sum the merged column and compare with the source total for the same keys.
| Order SKU | Price returned | Key returned | Count in price list | Status |
|---|---|---|---|---|
| A-17 | 4.5 | A-17 | 2 | Check: duplicate key |
| B-02 | 9 | B-02 | 1 | OK |
| C-40 | 12 | C-39 | 0 | Wrong: nearest match |
| D-11 | 7.25 | D-11 | 1 | OK |
Two of four rows need attention, and neither showed an error. In SQL the equivalent checks are a GROUP BY key HAVING COUNT(*) > 1 on the source and a row-count comparison after the join. Analistable joins in SQL in your browser and shows the query, so you can ask it to list keys that appear more than once in either sheet before trusting a merged answer.
Prevent wrong matches
- Use exact match everywhere unless you're deliberately looking up bands.
- Make keys unique in the source, or add the column that makes them unique.
- Reference Tables, not hand-typed ranges.
- Keep the verification columns in the workbook; they cost little and catch the next problem.
For choosing between lookup functions, see VLOOKUP vs XLOOKUP vs INDEX MATCH; for the full picture of joining files, the join spreadsheets guide.
Frequently asked questions
- Why does XLOOKUP return a value from a different row?
- The key appears more than once and XLOOKUP returns the first, the match mode allows nearest matches, or the return range starts on a different row from the lookup range.
- How do I make XLOOKUP return the last match instead of the first?
- Set search_mode to -1: =XLOOKUP(F2, A:A, C:C, "not found", 0, -1). It searches from the bottom up.
- Can XLOOKUP return all matches?
- No, it returns one. Use FILTER in Microsoft 365 or Google Sheets to return every matching row, or TEXTJOIN with FILTER to list them in one cell.
- Does XLOOKUP warn about duplicate keys?
- No. Check with COUNTIF on the lookup column before relying on the results.
- Why did my lookups change after I inserted rows in the source?
- Whole-column and Table references adjust automatically, but a range typed into a formula on another sheet can end up misaligned if only one of the two ranges was edited. Use Table column references so both ranges always move together.