Fixing Excel XLOOKUP That Returns #N/A When Match Mode Is Approximate

You switched from VLOOKUP to XLOOKUP expecting fewer headaches, and now your approximate-match formula returns #N/A on values that are clearly in range. The error isn't a bug in Excel — it's a sign that one of XLOOKUP's match mode rules is being violated in a way that's easy to miss.

XLOOKUP's match_mode argument gives you five distinct behaviors, and each one has its own preconditions. Get one precondition wrong and you get #N/A instead of a result, even when the lookup value logically should match something.

What you'll learn

• What each of XLOOKUP's five match mode values actually does under the hood
• Why sort order is mandatory for binary search modes — and how to detect violations
• How type mismatches (numbers stored as text, date serials, etc.) cause silent failures
• How to debug wildcard patterns that look correct but still return #N/A
• A repeatable checklist for diagnosing any XLOOKUP approximate-match error

What Approximate Match Mode Actually Means in XLOOKUP

XLOOKUP's syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The fifth argument, match_mode, controls how the function decides whether a candidate in lookup_array is a valid match. When you omit it, the default is exact match only — which surprises many VLOOKUP users who are used to approximate match being the default.

Approximate match does not mean "close enough". It means a precise algorithmic rule about which value to return when an exact hit does not exist. Understanding that rule for each mode is the foundation of every fix in this article.

The Five Match Mode Values — and Where People Go Wrong

Each integer you pass to match_mode triggers a different algorithm.

match_mode	Behavior	Common mistake
0 (default)	Exact match. Returns #N/A if no exact hit.	Forgetting to change this when a range lookup is needed.
-1	Exact match, or next smaller value.	Array is not sorted ascending — required for this mode to be reliable.
1	Exact match, or next larger value.	Array is not sorted ascending; also, lookup value exceeds the largest entry.
2	Wildcard match (*, ?, ~).	Wildcard characters in the lookup value are not literal; escaping is misunderstood.
-2 or 2 with search_mode 2 or -2	Binary search (fastest, requires sorted data).	Data is unsorted or partially sorted, producing wrong results or #N/A.

The single most common mistake is leaving match_mode at its default of 0 when you intend a range lookup. A salary band table, a tax bracket table, or a shipping tier table all need -1 or 1, not 0. If you accidentally omit the argument, Excel silently uses exact match and returns #N/A for every value that doesn't appear verbatim in the lookup array.

Why Sort Order Matters for Binary Search Modes

Match modes -1 and 1 do a linear scan by default, but they still require data to be sorted ascending to return correct results. When you also pass search_mode 2 (binary search ascending) or search_mode -2 (binary search descending), Excel skips values it has not inspected — and if the array is not sorted, those skipped values may be exactly the ones you need.

Consider this tax bracket table:

Minimum Income | Rate
0              | 10%
10000          | 12%
40000          | 22%
85000          | 24%
163000         | 32%

If rows are accidentally ordered as 0, 40000, 10000, 85000, 163000 (the 10k row moved down), an XLOOKUP with match_mode -1, search_mode 2 can return #N/A or the wrong rate for incomes between 10,000 and 39,999. The binary search exits early based on the assumption that data is ordered, so it never sees the 10,000 row.

Fix: Sort the lookup array ascending before using match_mode -1 or 1. If you cannot rearrange the source data, switch search_mode to 1 (linear, first-to-last) or -1 (linear, last-to-first) so Excel inspects every row. You trade a small amount of performance for correctness.

Descending sort with search_mode -2

If your table is sorted largest-to-smallest and you use search_mode -2, the array must be genuinely sorted descending. Mixed-sort or partially reversed data causes the same class of problem. Always verify the sort before adding a binary search mode.

Type Mismatches That Silently Break Approximate Lookups

A number and a text string that looks like a number are not the same thing to Excel. XLOOKUP will not coerce types during a lookup, so match_mode -1 on a column of numbers will return #N/A if your lookup value is the text "40000" instead of the number 40000.

This is the same root cause documented for other lookup functions — see the walkthrough on VLOOKUP returning #N/A when source data has trailing spaces for a deeper look at data-quality issues that affect lookup formulas generally.

How to spot a type mismatch

• Select a cell in your lookup array. Check the number format in the Home ribbon — if numeric values show as "General" but are left-aligned, they're stored as text.
• Use =ISNUMBER(A2) alongside the lookup column. If it returns FALSE for cells that look like numbers, you have text-stored numbers.
• Look for a small green triangle in the top-left corner of cells — Excel's built-in "number stored as text" warning.

Fixing text-stored numbers

The quickest repair is to select the column, open the warning dropdown (the yellow diamond icon), and choose Convert to Number. Alternatively, use a helper column with =VALUE(A2) and paste-as-values back into your lookup range. If the mismatch is in the lookup value itself (from a cell fed by another formula), wrap it: =XLOOKUP(VALUE(B2), ...).

Date serial mismatches

Dates are stored as integers in Excel. If your lookup array contains date-formatted cells but your lookup value comes from a formula that returns a text date like "2024-03-15", you'll get #N/A. Wrap the lookup value in DATEVALUE() or ensure both sides are true date serials.

Wildcard Match Mode Pitfalls (#N/A When Pattern Looks Correct)

match_mode 2 enables wildcard matching: * (any sequence of characters), ? (any single character), and ~ (escape character). This mode is different from the approximate numeric modes — it does a string pattern scan, and sort order is irrelevant.

The errors that produce #N/A here are almost always pattern construction mistakes:

• Pattern in the wrong place. Wildcards must be in the lookup_value, not in the lookup_array. =XLOOKUP("Smith*", A2:A100, B2:B100, , 2) works. Having the array contain Smith* as a literal string does not cause wildcard matching.
• Literal asterisk in the data. If your lookup array contains values like "10% off*" and you're searching for that literal string, the * in your lookup value will be treated as a wildcard. Escape it: "10% off~*".
• Case and accent sensitivity. Wildcard mode is case-insensitive for ASCII, but diacritics (accented characters) can cause unexpected misses depending on locale settings. Test with a simplified ASCII version if you're unsure.
• Leading or trailing spaces in the array. A pattern of "Smith*" will not match " Smith Johnson" (note the leading space). Trim the array with a helper column before using wildcard lookup.

Step-by-Step Debugging Checklist

Work through this checklist top-to-bottom whenever XLOOKUP returns #N/A with an approximate match mode.

1. Confirm the match_mode argument is set. Click into the formula bar and count the commas. If the fifth argument is missing or empty, Excel defaults to exact match (0). Add the intended mode explicitly.
2. Check data types. Run =ISNUMBER() and =ISTEXT() on a sample of lookup-array cells and on the lookup value cell. Both sides must be the same type.
3. Inspect for hidden characters. Paste your lookup value into a cell and apply =LEN(A1). Compare that to what you'd expect. Extra characters — especially non-breaking spaces (char 160) from web pastes — cause exact and approximate matches to fail.
4. Verify sort order for modes -1 and 1. Temporarily add a helper column with =A2>A1 dragged down. If any cell returns TRUE after a FALSE, the array is not monotonically sorted and binary search modes will misbehave.
5. Test with an exact lookup value first. Replace your lookup value with a cell that exists verbatim in the array and use match_mode 0. If that also returns #N/A, the problem is in the range reference or data type, not in the match mode at all.
6. Isolate the if_not_found argument. If you've wrapped the XLOOKUP in IFERROR or used the fourth argument to return a custom message, remove that temporarily so you see the raw error. An #N/A masked by a friendly message is harder to diagnose. The article on IFERROR hiding real errors explains why this matters.
7. Check for merged cells in the lookup range. Merged cells can confuse range references. Unmerge and retest.

Practical Examples With Fixes

Example 1: Tax bracket lookup returning #N/A

You have income brackets in A2:A6 and rates in B2:B6, both sorted ascending. Your formula:

=XLOOKUP(D2, A2:A6, B2:B6)

This returns #N/A for any income that isn't exactly one of the bracket thresholds because the default match_mode is 0. Fix it by specifying the next-smaller mode:

=XLOOKUP(D2, A2:A6, B2:B6, "Check input", -1)

Now an income of 52,000 correctly returns the 22% rate (the highest bracket at or below 52,000). The fourth argument provides a friendly fallback if someone enters a negative income below the first bracket.

Example 2: Product tier lookup failing due to text-stored numbers

Your lookup array contains order quantities imported from a CSV. They look like numbers but are stored as text. Your formula:

=XLOOKUP(F2, C2:C10, D2:D10, , -1)

Returns #N/A because the numeric lookup value in F2 cannot match text strings in C2:C10. Two fixes:

Option A — Convert the array at lookup time using ARRAYFORMULA-style coercion (works in newer Excel):

=XLOOKUP(F2, VALUE(C2:C10), D2:D10, , -1)

Option B — Fix the source data permanently. Select C2:C10, use the Convert to Number option, and the original formula works as-is. Option B is cleaner for ongoing use.

Example 3: Wildcard lookup for partial product code

You want to find the price for any product whose code starts with "SKU-42". Your lookup array is in A2:A200 (product codes) and prices in B2:B200.

=XLOOKUP("SKU-42*", A2:A200, B2:B200, "Not found", 2)

If this returns #N/A, run a quick trim check. If a code in the array is " SKU-4201" with a leading space, the pattern won't match. Clean the array with =TRIM() in a helper column, or modify the pattern to "*SKU-42*" to match the substring anywhere in the string.

This kind of lookup also relates to the pattern-matching issues covered in the article on INDEX MATCH returning the wrong row on sorted data — the sort and type rules apply across Excel's lookup family.

Example 4: If_not_found masking a deeper problem

Someone built this formula:

=XLOOKUP(H2, E2:E50, F2:F50, 0, -1)

It returns 0 for every row, which looks plausible in a numeric context. But the fourth argument is the number 0, not an error indicator — so when the lookup genuinely fails, you get 0 back silently. Change the fourth argument to a distinctive string like "LOOKUP FAILED" during debugging so failures are unmistakable. Once you've fixed the root cause, restore a sensible fallback.

If you work with SUMIFS as well as XLOOKUP, similar silent-failure patterns appear there too — the article on SUMIFS returning incorrect totals with wildcard criteria walks through a comparable debugging approach.

Wrapping Up: Next Steps

XLOOKUP's approximate match errors almost always trace back to one of three root causes: the wrong match_mode value (or none at all), unsorted data when binary or next-value modes require a sorted array, or type mismatches between the lookup value and the array. Wildcard mode adds pattern-construction mistakes to that list.

Here are four concrete actions to take right now:

• Audit your match_mode argument. Open any XLOOKUP formula that returns unexpected #N/A and count the arguments. Confirm the fifth argument is the integer you intend, not a blank.
• Validate data types before deploying a lookup. Add a temporary =ISNUMBER() column next to your lookup array. Delete it after you've confirmed types are consistent.
• Sort lookup arrays explicitly. Don't rely on the data arriving pre-sorted. Add a sort step (Data → Sort, or SORT() inside the formula) before any range lookup that depends on order.
• Use a descriptive if_not_found value during testing. Something like "DEBUG-MISS" is impossible to confuse with a real result, and it makes failures visible immediately.
• Document the expected match mode in a cell comment. Future maintainers (including yourself six months from now) will thank you for a note explaining why -1 was chosen and what sort order the table must maintain.