XLOOKUP function in Excel is a powerful function in Excel 365 that simplifies data lookup in tables. It improves VLOOKUP by allowing searches to the left, returning multiple values, and handling errors better.

What are the improvement of XLOOKUP

In this article, we have detailed the reasons why VLOOKUP returns #N/A, and there is more than one reason 😮

• Exact match by default: VLOOKUP defaults to an approximate match. XLOOKUP fixes this with exact match as the default.
• Lookup in any direction: VLOOKUP only looks to the right. XLOOKUP lets you search in any direction.
• Approximate search in both directions: VLOOKUP needs sorted data and only searches from lowest to highest. XLOOKUP works both ways without needing sorting.
• Handle errors: If no result is found, XLOOKUP lets you display a custom message instead of an error.

Why is it called XLOOKUP?

It combines the strengths of VLOOKUP and INDEX. Here’s why:

• X comes from the INDEX function.
• LOOKUP comes from the VLOOKUP function.

So, XLOOKUP is the combination of the best part of each functions 😉

How to use the XLOOKUP function

The XLOOKUP function has three required arguments and up to three optional ones:

1. lookup_value: The value you want to search for.
2. lookup_array: The range or array where XLOOKUP searches for the lookup_value.
3. return_array: The range or array from which XLOOKUP returns a corresponding value. It could be one or more columns
4. [if_not_found]: What to display if no match is found
5. [match_mode]: Defines the type of match - Exact match (default), Exact match or next smaller value, ...
6. [search_mode]: Specifies the search order

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

Let's see the most common case. We want to return the Area according to the ISO Code country. We need to fill the first 3 argument

1. The lookup value: the cell C11
2. Select the column where to search for this value, the ISO code column (or C2:C9)
3. The return column, the Area column (or F2:F9)

It's very easy 😃👍

You only select two columns: the lookup column and the return column, regardless of their position in the table. This allows you to return a column to the left of the lookup column, as you only need to reference the return column directly

Now, if the value you’re searching for is missing, use the 4th argument to display a custom message. For example, since the code 'SI' isn’t in column C, instead of showing #N/A, we display 'Unknown'.

Related Articles

• Search in 2 Columns with VLOOKUP & XLOOKUP
• VLOOKUP Left Lookup Using INDEX or XLOOKUP
• Return a Value Between Two Values in a Range