XLOOKUP Not Returning Value? Fixes & Reasons

Formulas & functions · Updated November 22, 2026

If your XLOOKUP is not returning a value, the most common causes are an exact match not being found, the lookup value being text that looks like a number (or vice versa), or the lookup array and return array being different sizes. Start by checking that your lookup value exists in the first column/range of the lookup array, then verify both arrays have the same number of rows (or columns, if horizontal).

How XLOOKUP Works

XLOOKUP searches a range (the lookup array) for a value and returns a corresponding value from another range (the return array). It was introduced in Excel 2021 and Microsoft 365, replacing VLOOKUP and HLOOKUP with a more flexible syntax. The basic structure is:

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

For example, with product names in A2:A10 and prices in B2:B10, to find the price of “Widget”:

=XLOOKUP("Widget", A2:A10, B2:B10)

This returns the price from the same row where “Widget” appears in column A. If “Widget” is not there, it returns #N/A by default. The function works vertically (rows) or horizontally (columns), and it can return a whole row or column if you give it a multi-cell return array.

Common Reasons XLOOKUP Returns #N/A

The #N/A error means the lookup value was not found. This happens for several reasons:

  • Exact match not found: XLOOKUP defaults to exact match. If your data has extra spaces, different case (though case is ignored unless you use match_mode 0 with a special trick), or the value is stored as text vs. number, it won’t match.
  • Lookup array is not the first column: Unlike VLOOKUP, XLOOKUP can search any column, but you must point the lookup array to the correct column. If you accidentally point to the wrong column, you’ll get #N/A.
  • Text vs. number mismatch: If your lookup value is a number but the data is stored as text (e.g., with an apostrophe or formatted as text), XLOOKUP treats them as different. Similarly, if your lookup value is text like “123” but the data is numeric 123, it fails.
  • Trailing or leading spaces: Invisible spaces in the data or in the lookup value cause mismatches. Use =TRIM() to clean data.
  • Wildcard characters: If you use an asterisk (*) or question mark (?) in the lookup value, XLOOKUP treats them as wildcards only if you set match_mode to 2 (wildcard match). By default, they are literal characters, so a value like “*Widget” won’t match “Widget”.

Why XLOOKUP Returns a Wrong Value (Not #N/A)

Sometimes XLOOKUP doesn’t return an error but returns an unexpected value. This usually happens when:

  • Match mode is set to approximate (1 or -1): If you set match_mode to 1 (exact or next smaller) or -1 (exact or next larger), XLOOKUP will return a close match when an exact match isn’t found. This is useful for ranges like tax brackets, but if you intended exact match, you’ll get wrong results. Always use 0 for exact match unless you need approximate.
  • Return array is misaligned: If your return array is not the same size as the lookup array, XLOOKUP returns a #VALUE! error or, if the arrays are partially overlapping, it might return a value from the wrong row. Ensure both arrays have the same number of rows/columns.
  • Duplicate lookup values: XLOOKUP returns the first match (by default, search_mode 1 searches from first to last). If you have duplicates and want the last match, use search_mode -1.
  • Data type coercion: If your lookup value is a number but the data is text, and you have match_mode 0, it fails. But if you accidentally use match_mode 2 (wildcard), it might treat the number as text and match incorrectly.

How to Fix XLOOKUP Not Working

Follow these steps to troubleshoot:

  1. Check for #N/A: If you see #N/A, the value is not found. Use =ISNUMBER(MATCH(lookup_value, lookup_array, 0)) to confirm. If it returns FALSE, the value isn’t there.
  2. Clean your data: Use =TRIM(A2) to remove extra spaces, and use =TEXT(A2,"0") to convert numbers to text if needed, or =VALUE(A2) to convert text to numbers.
  3. Verify array sizes: Select both arrays and check the number of rows. They must be equal. For example, if lookup_array is A2:A10 (9 rows), return_array must be B2:B10 (9 rows), not B2:B9.
  4. Set match_mode to 0: If you want exact match, write =XLOOKUP(lookup_value, lookup_array, return_array, "Not found", 0) to be explicit.
  5. Use the if_not_found argument: Add a custom message like "Value not found" to avoid the ugly #N/A and make it easier to spot issues.
  6. Check for hidden characters: Use =LEN(A2) to see if the length is longer than expected. If so, use =CLEAN(A2) to remove non-printable characters.

XLOOKUP vs. VLOOKUP and INDEX/MATCH

XLOOKUP is often easier than VLOOKUP because it doesn’t require the lookup column to be the first column, and it can return values to the left. It also has built-in error handling. Compared to INDEX/MATCH, XLOOKUP is simpler to write and read, but INDEX/MATCH is still useful in older Excel versions. For example, to do the same lookup with INDEX/MATCH:

=INDEX(B2:B10, MATCH("Widget", A2:A10, 0))

This works in all Excel versions, but XLOOKUP is more concise and handles errors better. If you’re using Excel 2019 or earlier, you cannot use XLOOKUP; you must use VLOOKUP or INDEX/MATCH.

Advanced XLOOKUP Tips

XLOOKUP can handle more complex scenarios:

  • Return multiple columns: If you want to return several columns, specify a return array like B2:C10 and the formula will spill the results.
  • Wildcard matching: Set match_mode to 2 and use * for any sequence or ? for a single character. For example, =XLOOKUP("Wid*", A2:A10, B2:B10, , 2) finds “Widget”.
  • Reverse search: Use search_mode -1 to find the last occurrence instead of the first.
  • Binary search for speed: On sorted data, use search_mode 2 (binary search) or -2 for faster performance on large datasets.
  • Combine with other functions: Use XLOOKUP inside IFERROR, or with FILTER for dynamic arrays. For example, to return all matches, use FILTER instead.

Frequently Asked Questions

Why does XLOOKUP return #VALUE! instead of #N/A?
#VALUE! usually means the lookup array and return array have different sizes. For instance, if lookup_array is A2:A10 (9 rows) and return_array is B2:B9 (8 rows), Excel can’t align them. Fix by making both ranges the same size.

Can XLOOKUP return a value from a column to the left of the lookup column?
Yes, that’s one of its advantages over VLOOKUP. You can set lookup_array to column C and return_array to column A, and it works fine.

Does XLOOKUP work with merged cells?
It can, but merged cells often cause issues because only the top-left cell holds the value. It’s better to unmerge and fill down the values to avoid unexpected #N/A errors.

How do I make XLOOKUP case-sensitive?
By default, XLOOKUP is case-insensitive. To make it case-sensitive, use =XLOOKUP(1, EXACT(lookup_value, lookup_array), return_array) as an array formula, or use a helper column with EXACT.

Is XLOOKUP available in Excel 2019?
No, XLOOKUP is only available in Excel 2021 and Microsoft 365. In Excel 2019, use VLOOKUP or INDEX/MATCH.

Why does XLOOKUP return a blank cell even though the value exists?
If the return cell is blank, it means the corresponding value in the return array is actually empty. XLOOKUP returns that empty cell, which displays as blank. To treat blanks as 0 or a message, wrap with IF: =IF(XLOOKUP(...)="", "No value", XLOOKUP(...)).

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.