XLOOKUP Exact Match Not Working? Fix Common Causes

XLOOKUP uses exact matching by default, so a failed result usually means the lookup value and the values in the lookup range are not truly identical, or the formula points to the wrong ranges. To make the setting explicit, use match mode 0 and then check the data, ranges, and error message.

How XLOOKUP exact match works

XLOOKUP searches a lookup array for a value and returns the corresponding item from a return array. Its default match mode is 0, meaning exact match: the lookup value must match a value in the lookup array. You do not have to add 0 to get an exact match, but including it can make the formula easier to read and audit.

For example, with IDs in A2:A4, names in B2:B4, and the ID to find in E2, suppose A2:A4 contains 1041, 1042, and 1043, B2:B4 contains Ari, Mina, and Joel, and E2 contains 1042. The formula returns Mina:

With the lookup value in E2, IDs in A2:A4, and names in B2:B4, use: =XLOOKUP(E2,A2:A4,B2:B4,"Not found",0). The result is Mina.

The fifth argument, 0, is match_mode. It is different from the sixth argument, search_mode, which controls the direction or method of searching. Leaving match_mode out does not make XLOOKUP approximate; it defaults to exact. This differs from the traditional VLOOKUP behavior when its optional final argument is omitted.

Start with the error message

The error is a useful clue. Check what Excel displays before changing the formula or altering the source data:

#N/A: XLOOKUP did not find a match. The values may differ in type or content, or the lookup range may not include the expected value.
#VALUE!: Check that the lookup and return arrays have compatible dimensions. For a vertical lookup, both should be vertical ranges with the same number of rows.
#NAME?: Excel may not recognize the function name, perhaps because the workbook is open in an Excel version that does not support XLOOKUP or the formula has a spelling error.
A surprising but valid result: Review match_mode, search_mode, duplicates, and the selected ranges. The formula may be working as written but searching a different way than intended.

If you add an if_not_found message, such as Not found, it replaces #N/A when no match exists. It does not fix a mismatch, and it does not suppress every other kind of formula error. During troubleshooting, a clear message can be easier to interpret than a raw #N/A.

Check that the values have the same data type

A number stored as text is not the same underlying value as a number. A cell can display 1042 in both cases, yet one value may be numeric and the other text. This commonly happens with IDs copied from a website or imported from a text file. Applying a number format changes how a value looks; it does not necessarily convert text into a number.

If E2 contains the text 1042 while A2:A100 contains numeric IDs, you can convert the lookup value to a number. With E2 holding the text ID, numeric IDs in A2:A100, and return values in B2:B100, use:

=XLOOKUP(VALUE(E2),A2:A100,B2:B100,"Not found",0)

If the lookup column contains text IDs instead, converting the lookup value to text may be more appropriate. With numeric 1042 in E2, text IDs in A2:A100, and results in B2:B100, use:

=XLOOKUP(E2&"",A2:A100,B2:B100,"Not found",0)

These approaches assume the values can be converted consistently. VALUE returns an error if its input is not a number in a format Excel recognizes. Before converting an entire column, consider whether leading zeros matter: an ID such as 001042 is often a text identifier, not a number. Converting it to a number removes the leading zeros and may make it impossible to match against the original text ID.

Look for spaces and invisible characters

Names and imported codes can contain spaces that are difficult to notice. A trailing space in a cell, for example, makes its text different from the same characters without that space. Data copied from web pages may also contain a nonbreaking space, which looks like an ordinary space but is not always removed by standard cleanup.

A practical fix is to create helper columns that clean both the lookup value and the source keys in the same way. With raw keys in A2:A100, return values in B2:B100, and the lookup key in E2, enter this in C2 and fill it down:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Then, with cleaned keys in C2:C100 and the raw lookup value in E2, use:

=XLOOKUP(TRIM(CLEAN(SUBSTITUTE(E2,CHAR(160)," "))),C2:C100,B2:B100,"Not found",0)

TRIM removes ordinary extra spaces at the beginning and end and reduces repeated internal spaces; CLEAN removes certain nonprinting characters; SUBSTITUTE replaces character 160, a common nonbreaking space, with a regular space. These tools do not solve every text-cleanup problem. If a code contains punctuation or other meaningful characters, do not remove them unless the data rules say they should be ignored. Keeping cleaned values in helper columns also makes it easier to inspect what Excel is matching.

Verify the ranges and their alignment

The lookup array and return array must describe corresponding records. If the lookup array starts on row 2 but the return array starts on row 3, XLOOKUP can return a value from the wrong row. If the arrays have incompatible shapes, Excel may return an error. Check both the starting and ending cells, as well as whether you selected the intended sheet and column.

For instance, with codes in A2:A100 and product names in B2:B100, an exact lookup should use those matching row ranges. Avoid using a whole-column lookup range alongside an unrelated or offset return range when a clearly aligned range is available. If you copy the formula down, decide whether the ranges should move: add dollar signs to make a range absolute when appropriate, as in A$2:A$100 and B$2:B$100. A relative reference that shifts unexpectedly is a common reason a formula works in one row but fails in the next.

Also confirm that the value you expect to find is actually inside the selected lookup array. A correct key elsewhere in the worksheet will not be found if the formula searches only part of the column.

Review match mode, search mode, and duplicates

Although exact match is the default, an explicitly entered mode can change the result. XLOOKUP’s match_mode values are 0 for exact match, -1 for exact match or the next smaller item, 1 for exact match or the next larger item, and 2 for wildcard matching. If the formula uses 1 or -1, it may return a nearby value when there is no exact match. If it uses 2, asterisks and question marks in the lookup value can act as wildcards rather than literal characters.

For ordinary exact lookup, use 0 or leave match_mode blank. If you need an exact literal search for a key that includes a wildcard character, the tilde character escapes wildcard characters in wildcard mode. Do not switch to wildcard mode just to get a missing exact match unless partial matching is genuinely intended.

Search mode is a separate setting. The default searches from first to last. A search mode of -1 searches from last to first, which changes which duplicate match is returned. Search modes 2 and -2 use binary search and require the lookup array to be sorted in the appropriate order; using them on unsorted data can produce invalid results. For a normal unsorted list, leave search_mode blank unless you have a specific reason to change it.

Exact match does not mean case-sensitive match. XLOOKUP’s standard text comparison does not distinguish uppercase from lowercase, so ABC and abc can match. If a list contains duplicates, the usual first-to-last search returns the first matching item; it does not report that multiple records share the same key. If duplicates matter, check the source data or use a method that returns multiple results, such as FILTER in supported Excel versions.

Check dates, formats, and formula calculation

Dates can look identical while having different underlying values. Excel stores dates as serial numbers, but a date imported as text may only resemble a real date. Similarly, a cell can display a rounded number while the stored value has more decimal places. When a date or number should match, check the underlying values and whether one side is text. A consistent date conversion or a helper column can help, but avoid changing formats alone and assuming the stored data has changed.

If the source value is itself produced by a formula, inspect that formula and its result. A formula that returns an empty string is not necessarily the same as a genuinely empty cell. Check for leading apostrophes in imported text and for unexpected characters in the formula bar. If the result appears stale after a source change, confirm that calculation is set to Automatic under Formulas > Calculation Options. You can also recalculate the workbook with F9 on Windows; on Mac, use Fn+F9 on keyboards where function keys require the Fn key. Recalculation will not repair a genuine mismatch, but it can help when the displayed result has not updated.

Confirm your Excel version and platform

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is also available in Excel for the web. Excel 2019 and Excel 2016 do not include XLOOKUP as a built-in function. If a workbook containing XLOOKUP is opened in an older version, that version may show an unsupported-function error. Updating Excel or opening the workbook in a supported version is the direct remedy; changing the formula’s match mode will not add the function to an unsupported edition.

The basic XLOOKUP syntax and exact-match behavior are the same in Microsoft 365 for Windows and Mac. Excel for the web also supports the function, though some menus and interface details differ from the desktop app. If the workbook is shared with people using older Excel versions, consider whether a compatible formula such as INDEX with MATCH is needed, and check the workbook in the versions those people actually use.

A quick troubleshooting sequence

Work through these checks in order rather than rewriting the formula immediately:

Read the error. For #N/A, investigate whether the key exists and whether it is identical in type and content.
Confirm the lookup value and lookup array are the intended cells. Verify sheet names, row limits, and alignment with the return array.
Check for numbers stored as text, text IDs with leading zeros, date values stored as text, and unwanted spaces or imported characters.
Inspect the formula arguments. Use match_mode 0 for exact matching, and remove an unintended wildcard or approximate mode.
Check for duplicates and search direction if Excel returns a result, but not the record you expected.
Confirm that the Excel version supports XLOOKUP and that the workbook has recalculated.

Once you identify the cause, fix the data consistently where possible. A helper column that standardizes imported keys is often safer than repeatedly adding complicated cleanup steps to each lookup formula. It also makes the cleaned key visible for review and can help prevent the same mismatch in other formulas.

Frequently Asked Questions

Does XLOOKUP default to exact match?
Yes. If you omit match_mode, XLOOKUP uses 0, which means exact match. You can enter 0 explicitly as the fifth argument to make that choice clear.

Why does XLOOKUP return #N/A when the value is visible?
The displayed values may differ underneath. Check for a number stored as text, leading or trailing spaces, nonbreaking spaces, text dates, or a lookup range that excludes the visible cell.

Is XLOOKUP case-sensitive?
No. Standard XLOOKUP matching does not distinguish uppercase from lowercase. If case must matter, use a case-sensitive approach rather than assuming exact match means case-sensitive.

Why does XLOOKUP return the wrong duplicate?
By default, it returns the first match it finds from the top of the lookup array. Use search_mode -1 to search from the end if the last duplicate is the intended result, or remove duplicates if the key should be unique.

Can XLOOKUP match part of a text value?
Yes, with match_mode 2, which enables wildcard matching. The asterisk represents any number of characters and the question mark represents one character. For ordinary exact matching, use match_mode 0 instead.

Why is XLOOKUP not recognized in my workbook?
The Excel version may not support it. XLOOKUP is included in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. Open the file in a supported version or use a compatible alternative formula.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.