Why XLOOKUP is not working properly

When the XLOOKUP is not working with your Excel version, please read our guide, and check the possible workaround and fixes.

XLOOKUP provides almost endless possibilities in Microsoft Excel. But the function in some circumstances brings difficulties. Take a closer look at the list below!

15 Reasons why XLOOKUP is Not Working

Here are the top five reasons why your function is not working. Look at your formula error and jump to the given section:

1. XLOOKUP does not appear or is not supported

XLOOKUP is only part of Microsoft365 subscriber licenses; ensure that you use the correct version. If you have a recent Excel version, use an add-in that implements the function.

XLOOKUP is not working properly or missing

It is easy to check your Excel version. First, click File > Account and look at the product information window. If your Excel version is not supported, please do not panic. Instead, use our add-in. It is fully compatible with all recent Excel releases.

2. #VALUE errors

Fixes Different array sizes using the TRANSPOSE function

In the example, you want to use the lookup function and find the correspondent record for Paris. Location and Price are named ranges referred to as C3:C8 and range F2:K2. The function will return a # VALUE error because the Location array size is 1×6 and the Price array size is 6×1.

xlookup is not working with different array sizes

To fix the formula, use a TRANSPOSE function and convert the Price range to a column. I inserted the transposed “virtual array” in column D to make this example easy to understand.

fix the formula using transpose function

Row size differences

XLOOKUP is not working properly if you use different row or column sizes. For example, the lookup array (C3:C8) contains six rows, but the return array (D3:D7) has only five rows. The array’s size difference is the reason for the appearing #VALUE error in cell G7.

It’s easy to check your setup. But, first, you should have to click on the Formula Bar.

array size differences

Excel will highlight all arrays. First, you need to add the last row in column D in the lookup array. After that, the formula will return the estimated value.

Function Argument is not valid

As we know, the function uses three required and three optional arguments. Here are the possible values for the 5th and 6th arguments:

  • match mode: {0, -1, 1, 2}
  • search mode: {1, -1, 2, -2}

Look at the formula below! We are using “4” as match mode. Because it is not valid, we’ll get a #VALUE error.

function argument value not accepted

3. #N/A errors

The main reasons for getting #N/A errors:

  • No exact match
  • No approximate match
  • Numbers stored as text
  • The lookup array is not sorted
  • The formula contains unnecessary or extra characters

Missing error-handling in case of no exact match

If you are not using the match_mode argument, XLOOKUP looks for an exact match. If the function does not find the lookup value in the lookup array, the formula will return the #N/A Error.

xlookup is not working value is not found in the lookup array

To avoid these errors, use a custom text for error-handling purposes (like “value not found”).

No Approximate Match

In the example below, set the match_mode to 1. Then, the function tries to find an exact match. In case of no exact match, it will return the next largest value from the lookup array that is greater than the lookup value.

no approximate match found

We have no exact match, and if all values in the lookup array are less than the lookup value, the function will return an #N/A error.

When XLOOKUP is not working, try to use the 4th argument to handle errors, and the formula will return an easily understandable message.

value not found

Different Data Types

The most important rule:

lookup value data type = lookup array data type

For example, if your lookup value is a number, the function will work only if the lookup array contains numbers. In the picture below, our lookup value looks like a number. No, not that all. In cell G7, we have a number that Excel store as text.

XLOOKUP is not working different data types

We have to apply a quick conversion. To do that, apply these steps:

  1. Select the cell that contains the lookup value
  2. Locate the triangle icon on the top-left corner of the cell
  3. Use the Convert to Number option
convert text to number

Your function will work again!

Unwanted characters in lookup arrays

Working with formulas is easy, but in some cases, you have a non-printable or unwanted character in the lookup or return arrays. These characters, like spaces, do not make your life easier.

In the example below, everything looks great, but we are getting an #N/A error even if the lookup array contains the lookup value.

What might be the problem with the formula? In this case, it is worth using the LEN function to check the array integrity.

Hint: the C3 cell contains two extract spaces, so “New York “ is not equal to “New York“.

xlookup is not working extra spaces

The good news is that we can clean the data on the fly using the TRIM function.

Here is the solution:

Our formula now works fine!

Unsorted records in the lookup array

In the following example, you are using -2 as the 6th parameter to lookup a value in a lookup array. Good to know that the binary_search parameter requires a sorted array.

Unsorted records in the lookup array

To solve this tiny but frustrating problem, you have two ways:

  • manually sort the records in the array
  • using the SORT and SORTBY function

In this guide, we’ll explain the function-based solution:

=XLOOKUP(G5,SORT(Price), SORTBY(Location,Price),,,2)

fix unsorted records in the lookup array

Both functions sort the selected arrays using ascending orders. The main difference between SORT and SORTBY is:

  • SORT returns with an array
  • SORTBY returns with a part of an array; in this case, the Price column
SORT vs SORTBY

Both functions create a dynamic array for the lookup_array and return_array.

4. #NAME errors (typo, human errors)

The #NAME error may appear in the following cases:

  • Typo with names
  • Missing or incorrect usage of named ranges
  • Comma or semicolon issue

The function has a typo

We added XLOOOKUP instead of the proper function name in the formula example.

xlookup is not working typo

The formula in cell G5 will return an #NAME error.

Missing or incorrect usage of named ranges

At first look, the formula looks correct, but it returns an error. First, make sure that the named range exists! It is easy to check whether the named range is valid or not.

missing or incorrect named range

Locate the Formulas Tab on the ribbon. Click Name manager! You can review all named ranges in the given Worksheet or the complete Workbook. Because “Location” <> “Locations”, it cause #NAME errors.

Comma or semicolon issue

When your XLOOKUP is not working, think about it: Which is the correct separator between function arguments? Comma or semicolon? It depends on your Excel version.

Good to know that only the US English version of Excel uses a comma (,) to separate arguments. International versions use a semicolon (;) by default.

comma or semicolon

We use the US-English version of Microsoft Excel in the example, so we’ll get an error. To fix it, use a comma to separate the arguments.

5. #SPILL

You frequently faced the #SPILL error. The main cause: the result array can not overwrite other cells if there are not blank.

Output array contains data

In the example, you want to use multiple lookup values, and the function works fine. The result is an array!

no spill error

What if one or more possible output cells contain text values or numbers?

a cell we need to spill data into is not blank

Move the mouse over the yellow triangle. The error message is the following: “A cell we need to spill data into is not blank”.

Excel Table does not support dynamic array formulas

In the example, you use an Excel table with multiple lookup values. Don’t do that.

xlookup is not working tables

Tip: Instead of using multiple lookup values, use a single value, Excel will copy the results until the end of the table without getting an error.

Thank you for being us today; we hope this guide was helpful and your formulas will work fine.

Additional resources

Istvan Vozar

Istvan is the co-founder of Excelkid. He writes blog posts and helps people to reach the top in Excel.