Fixing Excel XLOOKUP Returning #N/A When Match Mode Is Wrong

Your XLOOKUP formula looks correct. The value you're searching for is sitting right there in the column. Yet Excel stubbornly returns #N/A. Before you rewrite the whole formula, check the fifth argument — match_mode — because that single parameter is responsible for a large share of XLOOKUP failures that are otherwise invisible.

What you'll learn

• How the four match_mode values actually behave and when each one fails silently
• The specific scenarios that cause #N/A for each mode
• How to diagnose which mode is wrong for your data
• Practical fixes including wildcard matches, approximate matches, and IFERROR wraps
• Common pitfalls that look like match mode problems but aren't

A quick refresher on XLOOKUP syntax

The full signature is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The fifth argument, match_mode, accepts four numeric values. Most tutorials only cover the default, which is why the others catch people off guard.

When you omit the argument entirely, Excel uses 0. That default is correct most of the time, but it will silently fail the moment your data doesn't match exactly — including differences in whitespace, case sensitivity nuances, or number formatting.

Scenario 1: Exact match failing because of invisible differences

The most common reason match_mode = 0 returns #N/A has nothing to do with the mode itself — the lookup value and the table value look identical but aren't. Here's how to check.

Trailing spaces

Copy the cell value into a formula like =LEN(A2) and compare it to what you expect. If