Fixing Excel XLOOKUP That Returns Wrong Value When Search Array Has Merged Cells

You write a clean XLOOKUP formula, it runs without errors, and the value it returns is just wrong. No #N/A, no #VALUE! — just a quietly incorrect answer that can corrupt an entire report before you notice. Nine times out of ten, the culprit is merged cells in the search array.

Merged cells are one of those Excel features that look tidy in a presentation but silently sabotage formulas. XLOOKUP is not immune, and the failure mode is worse than an error because it gives you a result you might trust.

What You'll Learn

• Exactly how merged cells distort the data XLOOKUP scans
• How to reproduce the problem so you can verify your fix
• Three practical fixes ranked from simplest to most defensive
• Common traps people fall into when working around this issue

Why Merged Cells Break XLOOKUP Silently

Most Excel users know merged cells can cause problems, but the mechanism isn't obvious. When you merge a range of cells — say A2:A5 — Excel stores the actual value only in the top-left cell of that merged block. Every other cell in the merge (A3, A4, A5) is treated as empty internally, even though they appear filled on screen.

XLOOKUP scans its search array one cell at a time. When it hits those visually filled but internally empty cells, it does not find the value you're searching for. Depending on your match_mode setting, it either skips them or matches the wrong row entirely. The result comes back as a value from the wrong row — and Excel reports no error because it technically found a match.

This is a subtler issue than what you'd encounter with INDEX MATCH, but the root cause is identical. If you've already read about fixing Excel INDEX MATCH that returns #N/A when the lookup range has merged cells, the underlying principle will be familiar — XLOOKUP just fails differently because of how it handles non-exact matches.

How Merged Cells Actually Store Data in Excel

Open a blank sheet and type Sales in A2. Then merge A2:A5. The word "Sales" visually spans all four rows. Now click each cell individually and look at the formula bar:

• A2 — shows "Sales"
• A3 — shows nothing (empty string)
• A4 — shows nothing
• A5 — shows nothing

That's the core of the problem. The display is cosmetic; the data model is broken from a formula perspective. Any function that iterates over a range — XLOOKUP, MATCH, VLOOKUP — will only ever find the value at the top-left cell of each merged block.

Reproducing the Problem Step by Step

Build this minimal example to see the failure clearly before you fix it.

1. In column A, enter: A1 = Department, then merge A2:A4 and type Finance, merge A5:A7 and type Engineering, merge A8:A9 and type Marketing.
2. In column B, enter employee names in B2:B9: Alice, Bob, Carol, Dana, Eve, Frank, Grace, Hiro.
3. In column C, enter salaries: 90000, 85000, 82000, 95000, 101000, 99000, 78000, 88000.
4. In cell F2, type Engineering.
5. In cell G2, enter: =XLOOKUP(F2, A2:A9, B2:B9).

You expect "Dana" — the first employee in Engineering. Instead you get "Alice", because XLOOKUP finds "Finance" in A2, "Engineering" in A5... wait, actually it finds an empty cell at A3, and depending on match mode, it may return Alice, or it may skip to A5 but only if exact match is set. The specifics vary by match mode, but the outcome is that your search array has large gaps, and XLOOKUP is working with incomplete data.

The same silent failure affects VLOOKUP with merged lookup columns — if you want a comparison, see how VLOOKUP behaves when the lookup column has duplicate or missing keys, which is structurally similar.

Fix 1: Unmerge and Fill Down

This is the cleanest fix and the one you should use whenever the merged cells are in a column that formulas reference. Unmerge the cells and populate every row with its real value. Here's a fast way to do it:

1. Select the entire column that has merged cells (e.g., column A).
2. Go to Home > Alignment > Merge & Center dropdown and choose Unmerge Cells.
3. With the column still selected, press Ctrl+G (Go To), click Special, then select Blanks and click OK.
4. Without clicking anywhere, type =A2 (or whichever is the first cell in your range), then press Ctrl+Enter to fill all blank cells with the value from the cell above.
5. Copy the entire column, then Paste Special > Values to replace the formulas with static text.

After this, every row has its own explicit department value, and XLOOKUP scans the column cleanly. Your formula =XLOOKUP(F2, A2:A9, B2:B9) will now return the correct result.

Why paste as values? The =A2 formula in the blank cells creates relative references that will shift if you sort or move the data. Converting to static values removes that fragility.

Fix 2: Use a Helper Column to Normalize the Search Range

If you cannot modify the source layout — maybe the file is shared, or the merged cells serve a visual purpose you need to preserve — a helper column lets you feed XLOOKUP a clean search array without touching the original data.

Assume your merged department column is A2:A9. Add a helper column in, say, column D:

D2: =IF(A2="", D1, A2)
D3: =IF(A3="", D2, A3)
... (copy down to D9)

Or more concisely, enter this in D2 and copy it down:

=IF(A2<>"", A2, D1)

This formula says: if cell A2 has a value, use it; otherwise carry forward the value from the cell above. The result is a clean column with every row explicitly labeled, even though the source column has blanks from merging.

Now point XLOOKUP at the helper column:

=XLOOKUP(F2, D2:D9, B2:B9)

This works reliably and does not disturb the visual layout of your original table.

Fix 3: Wrap XLOOKUP with IFERROR and a Secondary Lookup

This is a defensive pattern for situations where the data comes from an external source you refresh regularly, and you cannot guarantee merged cells will be cleaned up every time. It is more of a patch than a proper fix, but it can save a report from returning a wrong value in production.

The idea: use XLOOKUP first; if it returns a blank or a value that doesn't match your search term, fall back to a second XLOOKUP that searches from the position of the first empty cell it hit. In practice, the helper column approach (Fix 2) is cleaner — but if you need belt-and-suspenders behavior, combine it with IFERROR:

=IFERROR(
  IF(XLOOKUP(F2, A2:A9, A2:A9)=F2, XLOOKUP(F2, A2:A9, B2:B9), "Check merged cells"),
  "Not found"
)

The inner IF verifies that what XLOOKUP matched in the search array actually equals your lookup value. If it doesn't — because XLOOKUP matched an empty cell via approximate matching — it returns a warning string instead of a silently wrong name. This doesn't give you the right answer automatically, but it stops you from trusting a wrong one.

When XLOOKUP Returns the First Cell Value Only

A related symptom: you search for a value that appears in the merged block, and XLOOKUP always returns the result for the very first row of the merge, regardless of which specific row in that block you wanted. This happens because all rows in the merge except the first are empty — XLOOKUP matches the first non-empty row and stops.

If your intent is to retrieve a result based on a secondary column (like employee name) within a department group, XLOOKUP with a single merged search array is the wrong tool entirely. Use a two-criteria approach:

=XLOOKUP(1, (D2:D9=F2)*(B2:B9=G2), C2:C9)

Here, D2:D9 is the filled helper column from Fix 2, F2 is the department, G2 is the employee name, and C2:C9 is the salary column. The formula returns 1 only where both conditions are true, and XLOOKUP finds that exact row. This is far more precise than relying on a merged column to define groups.

Multi-criteria XLOOKUP patterns are also relevant when you're working with array formulas — the behavior has similarities to the INDEX MATCH #VALUE! error that appears when an array formula spans multiple columns.

Common Pitfalls to Avoid

Re-merging after fixing

It sounds obvious, but it happens often. You unmerge, fill down, verify the formulas work — then someone re-merges the column to "make it look nicer" before sending the file. The fix evaporates instantly. If appearance matters, use Center Across Selection instead of Merge & Center. It looks nearly identical but does not merge the underlying cells.

Approximate match mode hiding the problem

XLOOKUP's default match mode is exact match (match_mode = 0). If you or a colleague has set it to approximate match (-1 or 1), the blank cells from merged blocks may trigger a nearest-neighbor match that returns a completely unrelated row. Always confirm your match mode when debugging unexpected results.

Assuming the problem is with the return array

When XLOOKUP returns a wrong value (not an error), most people inspect the return array first. With merged cells, the problem is almost always in the search array. Check the search array column first: select it, go to Home > Find & Select > Go To Special > Blanks, and see if blank cells are scattered through what should be a fully populated list.

Helper column not anchored correctly

If you use the helper column approach and copy the fill-down formula, make sure the reference to the cell above is relative, not absolute. =IF(A3<>"", A3, D2) in row 3 should refer to D2 relatively so it shifts as you copy down. A dollar sign on the row number (D$2) will break the carry-forward logic after the first row.

This kind of subtle reference error is similar to range offset issues that cause other lookup functions to misbehave — the same class of problem that causes SUMIF to return zero when the sum range offset doesn't match the criteria range.

Wrapping Up

XLOOKUP returning a wrong value due to merged cells in the search array is a silent failure — no error flag, just bad data. The fix is straightforward once you understand the mechanism: merged cells leave empty values in all but their top-left cell, and XLOOKUP can only work with what's actually there.

Here are the concrete actions to take right now:

• Audit your search array column using Go To Special > Blanks to confirm merged cells are the cause before you start changing formulas.
• Unmerge and fill down if you control the data layout — this is the most durable fix and costs you nothing in formula complexity.
• Add a helper column if the file structure is shared or locked, and point XLOOKUP at the helper instead of the merged column.
• Replace Merge & Center with Center Across Selection in any file where lookup formulas depend on that column, to prevent the problem recurring.
• Add a verification check using the IFERROR + IF pattern if the file ingests external data that may arrive pre-merged and you want a warning before wrong values propagate.