Excel XLOOKUP Not Finding Match? Fixes

Formulas & functions · Updated November 22, 2026

If your Excel XLOOKUP is returning #N/A or an unexpected result, the most common causes are mismatched data types (text vs. number), extra spaces or invisible characters, an incorrect lookup_array range, or the if_not_found argument being omitted. Start by checking the exact values in your lookup column and the lookup value—use =A2=B2 to test equality—then verify the lookup array covers the correct rows.

How XLOOKUP Works

XLOOKUP, introduced in Excel 2021 and Microsoft 365, searches a range or array for a lookup value and returns a corresponding value from another range. Its syntax is:

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

Unlike VLOOKUP, XLOOKUP can search to the left or right, handle arrays natively, and does not require the lookup column to be the first column. It defaults to an exact match (match_mode = 0), which is why data mismatches cause #N/A.

Common Reasons for #N/A

  • Text vs. number: If your lookup value is a number stored as text (e.g., "123") and the lookup array contains real numbers, XLOOKUP won’t match. Convert one side with =VALUE(A2) or =TEXT(A2,"0").
  • Extra spaces: Leading, trailing, or multiple internal spaces break matches. Use =TRIM(A2) to clean both the lookup value and the lookup array.
  • Invisible characters: Non-breaking spaces (CHAR(160)) or line breaks (CHAR(10)) can hide in cells. Use =CLEAN(TRIM(A2)) to remove them.
  • Wrong lookup_array: If the lookup array doesn’t include the row where the match exists, you’ll get #N/A. Verify the range covers all relevant rows.
  • Case sensitivity: XLOOKUP is case-insensitive by default. If you need case-sensitive matching, set match_mode to 1 (exact match, case-sensitive) – but note that this requires the lookup_array to be sorted.

How to Diagnose the Problem

Start by selecting the lookup value cell and the first cell of the lookup array, then type =A2=B2 in a blank cell. If it returns FALSE, the values are not identical. Check the cell format: right-click, Format Cells, and see if the Number tab shows Text for the lookup value. Use =LEN(A2) and =LEN(B2) to compare lengths; if they differ, there are hidden characters. Also, use =ISNUMBER(A2) to see if the value is numeric.

Fixing Data Type Mismatches

If you have a column of numbers stored as text (often marked with a green triangle in the corner), select the column, go to Data > Text to Columns, and click Finish to convert them to numbers. Alternatively, use =VALUE(A2) in a helper column. For text values, use =TEXT(A2,"0") to force a number to text. Always apply the same conversion to both the lookup value and the lookup array.

Cleaning Up Messy Data

To remove extra spaces and non-printing characters, create a helper column with =TRIM(CLEAN(A2)). Then use that helper column as the lookup array. If you have non-breaking spaces, replace them: =SUBSTITUTE(TRIM(CLEAN(A2)),CHAR(160),""). Copy the cleaned values and use Paste Special > Values to replace the originals.

Using XLOOKUP with Wildcards and Approximate Matches

If you need a partial match, set match_mode to 2 (wildcard match). Then you can use * for any sequence and ? for a single character. For example, =XLOOKUP("*"&D2&"*", A2:A20, B2:B20, "Not found", 2) finds any cell containing the text in D2. For approximate matches (like finding the nearest lower value), set match_mode to -1 (exact or next smaller) or 1 (exact or next larger), but the lookup array must be sorted accordingly.

Alternatives to XLOOKUP

If XLOOKUP isn’t available (older Excel versions), use INDEX/MATCH: =INDEX(return_range, MATCH(lookup_value, lookup_array, 0)). This works in all versions. For a simple left lookup, VLOOKUP can work but requires the lookup column to be the first column in the table. For multiple criteria, use INDEX/MATCH with array formulas or the FILTER function (Excel 365).

Frequently Asked Questions

Why does XLOOKUP return #N/A even though I can see the value? The value might be formatted as text in one place and number in another, or there are hidden spaces. Check with =A2=B2 and clean the data.

Can XLOOKUP be case-sensitive? Yes, set match_mode to 1, but the lookup array must be sorted in ascending order for that mode to work correctly.

How do I make XLOOKUP return a blank instead of #N/A? Use the if_not_found argument: =XLOOKUP(D2, A2:A20, B2:B20, "") returns an empty string.

Does XLOOKUP work on Mac Excel? Yes, XLOOKUP is available in Excel for Microsoft 365 on Mac, with the same syntax. Keyboard shortcuts differ (e.g., Cmd instead of Ctrl), but the function works identically.

Why does XLOOKUP return the wrong value, not an error? This usually happens when the lookup array contains duplicates. XLOOKUP returns the first match found. To get the last match, use search_mode = -1 (search from last to first).

Can I use XLOOKUP with a whole column like A:A? Yes, but it may slow down calculations. Better to use a specific range like A2:A1000.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.