XLOOKUP Not Working? 5 Proven Fixes

If your XLOOKUP formula returns an error or an unexpected result, the fix is usually one of five things: a missing exact match, mismatched data types, a range size mismatch, a closed workbook, or a version of Excel that doesn’t support the function. Here are the five proven solutions to get XLOOKUP working again, with step-by-step instructions for each.

Why XLOOKUP Fails: The Most Common Causes

XLOOKUP is a powerful replacement for VLOOKUP and HLOOKUP, but it has its own quirks. The most common reasons it fails are:

• No exact match found – by default, XLOOKUP looks for an exact match, and if it can’t find one, it returns a #N/A error.
• Data type mismatch – the lookup value might be text while the lookup array contains numbers (or vice versa).
• Different array sizes – the lookup array and return array must be the same size, otherwise you get a #VALUE! error.
• Closed workbook – if the lookup array is in another workbook that is closed, XLOOKUP returns a #REF! error.
• Outdated Excel version – XLOOKUP is only available in Excel 2021 and Microsoft 365. Older versions don’t recognize the function.

Solution 1: Add the Exact Match Argument

By default, XLOOKUP uses an exact match (the match_mode argument is 0). If you want an approximate match (like the closest smaller value), you need to specify it. But if you’re getting #N/A and you expect an exact match, the problem is that the lookup value simply isn’t in the lookup array. Check for:

• Extra spaces (use =TRIM(A2) to clean).
• Non-printing characters (use =CLEAN(A2)).
• Case differences – XLOOKUP is case-insensitive, so that’s not the issue, but text vs. number formatting is.

If you want to allow approximate matches, use =XLOOKUP(lookup_value, lookup_array, return_array, , -1) for the next smaller item or 1 for the next larger. But be careful: the lookup array must be sorted in ascending order for approximate matches to work correctly.

Solution 2: Fix Data Type Mismatches

If your lookup value looks like a number but is stored as text (or vice versa), XLOOKUP won’t find it. For example, if cell A2 contains the number 1234 and your lookup array contains the text “1234”, XLOOKUP returns #N/A. To fix this:

• Convert the lookup value to the same type as the lookup array. If the array contains numbers, use =VALUE(A2) to convert text to a number.
• If the array contains text, use =TEXT(A2, "0") to convert a number to text.
• Or use the #VALUE! error to identify the mismatch – but the easiest fix is to ensure both are the same type.

Solution 3: Check Array Sizes

XLOOKUP requires that the return_array be the same size as the lookup_array. If they aren’t, you get a #VALUE! error. For example, if your lookup array is A2:A100 and your return array is B2:B99, the sizes don’t match. To fix:

• Make sure both ranges have the same number of rows (and columns if you’re returning multiple columns).
• Use entire columns if you’re unsure: =XLOOKUP(A2, A:A, B:B) – but be aware this can slow down large workbooks.

Solution 4: Open the Source Workbook

If your XLOOKUP references a cell in another workbook, that workbook must be open. If it’s closed, Excel returns a #REF! error. To fix this:

• Open the source workbook and keep it open while you use the formula.
• Alternatively, copy the data into the current workbook to avoid external references altogether.

Solution 5: Update Excel or Use a Fallback

XLOOKUP is not available in Excel 2016, 2019, or earlier versions. If you’re using an older version, you’ll see a #NAME? error. To fix this:

• Update to Microsoft 365 or Excel 2021 to get XLOOKUP.
• If you can’t update, use INDEX/MATCH as a workaround: =INDEX(return_array, MATCH(lookup_value, lookup_array, 0)).

Frequently Asked Questions

Why does XLOOKUP return #N/A even though the value exists?

This usually happens due to data type mismatch or extra spaces. Check if the lookup value is text while the array contains numbers, or vice versa. Use =TRIM() and =CLEAN() to remove invisible characters. Also, ensure the lookup array doesn’t contain numbers stored as text.

Can XLOOKUP return multiple values?

Yes, if you give it a range for the return array, it will return all matching values. For example, =XLOOKUP(A2, A:A, B:C) returns two columns. But if you want to spill multiple rows, you need to use the # operator or dynamic arrays.

Is XLOOKUP case-sensitive?

No, by default XLOOKUP is case-insensitive. If you need case-sensitive matching, you’ll need to use EXACT in an array formula or use INDEX/MATCH with EXACT.

Why does XLOOKUP return #VALUE! when I use approximate match?

Approximate match requires the lookup array to be sorted in ascending order. If it isn’t, you’ll get a #VALUE! error. Sort the lookup array or use exact match instead.

Can I use XLOOKUP with wildcards?

Yes, you can use wildcards (* and ?) in the lookup value if you set the match_mode to 2. For example, =XLOOKUP("*"&A2&"*", A:A, B:B, , 2) finds a partial match.

Why does XLOOKUP return #REF! when I copy the formula to another sheet?

This often happens when the return array is not the same size as the lookup array after copying, or when the formula references a closed workbook. Check the ranges and ensure the source workbook is open.

Related

• Project Zomboid Dedicated Server vs Rented Hosting
• Arc Raiders Shared Watch Event: Merits & All Rewards
• How Stellantis Makes Money Across Its Brands