How To Use The NEW XLOOKUP Function in Excel [With Examples]

XLOOKUP is Excel’s new superhero Lookup function.

That said, it already replaced the use of VLOOKUP and INDEX, MATCH functions.

So, to start today, we will be guiding you through some amazing ways to use the new and improved XLOOKUP function in Excel.

Is Xlookup Better than VLOOKUP or HLOOKUP?

Yes, Xlookup outperforms Vlookup and Hlookup as the new Xlookup function can perform two-way lookups, find exact, partial, and even wildcard matches. Not only that, the Xlookup function can return values even if the dataset is organized horizontally.

Look at the initials of the Lookup functions.

VLookup stands for Vertical Lookup, HLookup stands for Horizontal Lookup. Xlookup stands for Xtended Lookup and that is why it is considered as an advanced function which actually outperforms the rest.

Some advanced features of the Xlookup function includes-

• Two-way lookup
• Allows to use Wildcard entries using the Search mode
• Return array
• Dynamic array
• Specifying a default value

Syntax of the Xlookup Function

There are six arguments in the Xlookup function but only three are required. Others are optional (depending on criteria).

The syntax for using Excel’s Xlookup is:

Let us look at the description of each of the arguments in a tablature and see how the arguments work.

Argument	Description	Required/Optional Fields
lookup_value	The value you are looking for	Required
lookup_array	The range where the value is the present	Required
return_array	The result we want in return	Required
[if_not_found]	What we want when the lookup value doesn’t exist in the lookup range	Optional
[match_mode]	Specifies how the lookup_value should be matched in the lookup_array. 0 – shows exact match. If not found, then return #N/A. -1 – Exact match or return the next smaller item. 1 – Exact match or return the next larger item 2 – wildcard character match	Optional
[search_mode]	Specifies whether the function should perform a search from the beginning or end of the lookup_array. There are 4 search options. 1 – Perform a search from first to last. -1 – Perform a search from last to first. 2 – Perform a binary search in ascending order -2 – Perform a binary search in descending order	Optional

How To Use Xlookup Function in Excel Efficiently [4 Examples]

We will demonstrate 4 solid examples that will help you take better control over this new function.

To start, let’s look at the first example.

Example 1: Troubleshoot Values That Are #N/A

You may be new to Xlookup. But at least once you created a table array argument while using VLOOKUP.

It’s a common problem where Excel returns #VALUE!, REF!, #N/A, and even a 0 value. You can use the advanced function (XLOOKUP).

To show you how, let’s look at a table where we will be teaching how to use the xlookup function in excel between two sheets.

Here, in the first sheet, we have the Employee Names and their Yearly salary. We want to identify the Maximum bonus amount of those employees.

On the contrary, in the second sheet we have the employee IDs, employee names (not sorted sequentially), and the Bonus Percentage (not same for all).

In this case, we will just be using the Lookup function based on the names.

So, follow the steps along.

1. Type the =XLOOKUP function in the blank cell C2. Select the lookup value based on the name.
2. Select and Lock the lookup array from sheet 2 because the second sheet contains the employee names and bonus percentage data.
3. Determine and lock the return array based on the bonus percentage. Use F4 to lock the values.
4. Close the bracket and press Enter.
5. Go to the Formatting options using Ctrl+1 and select the formatting style in Percentage format.
6. Drag the fill handle to gain all the necessary data.

Look at the not available errors. This error occurred because the lookup value was not present in the lookup array table.

Remember, we said that only 3 of the 6 arguments are required to run the XLOOKUP?

To remove such errors, we will now use the fourth argument.

If the lookup value is not found then we want Excel to return 0.

Now, since we want the salary amount, we will multiply the percentage with the yearly salary. So, the overall formula turns to be-

You can use the XLOOKUP for rows. That is, if your dataset is organized in rows instead of columns, you can use the same method to find the exact match.

This phenomenon replaces the use of HLOOKUP Function.

Example 2: Use Wildcards in the LOOKUP

We use wildcards when we don’t know the exact character. Two most commonly used wildcards are asterisk (*) and question mark (?).

In this example, we will use the asterisk sign (*) which is a placeholder of variable length of characters.

Let’s look at the previous example.

The least bonus winner was someone called Ali. Let’s use a wildcard to find out what his exact name was.

So, select an empty cell and type the name you recall (in our case, it’s Ali). Now select another blank cell and fire up the Xlookup function.

Note: Since we will use wildcards, the wildcard (*) will be a part of the lookup value.

So, our formula will be =XLOOKUP(“*”&D3&”*”,…).

Here we used two wildcards because we remember the name Ali was in the middle. You can eliminate the excess wildcard if there aren’t any surnames.

Our lookup range and return value are the same. Also, the fourth argument is not necessary. We will be using the fifth argument that is the match_mode.

As told earlier, the match mode argument has four options. Even though you use wildcard entries, you have to use the fourth option (2) that is the wildcard character match.

So, overall the formula will be-

Example 3: Perform a Two-Way Lookup With XLOOKUP

Let’s extend the previous example a bit. Add the Maximum Bonus amount with the Yearly Salary. You can enter the formula without entering any function and end up getting the maximum payment amount.

To make our selection process easier, we will create a dynamic list using the Data validation process. Go to Data > Data Validation > Allow List > Source (select the employee names for the first list and select the headers for the second list).

Now, select an empty cell underneath the two lists where we want the data to show. We are ensuring for a two-way lookup.

We will be using the LOOKUP Function twice.

Type =XLOOKUP(E4,A2:A10,…) where E4 is the lookup value of employee name (which we recently created a dynamic list of) and A2:A10 is the range of the lookup array.

For the return value, we have to launch the XLOOKUP Function once again because the return value could be any of the whole data set.

We want the exact match as E5 (which is the lookup value of yearly salaries list created from the dynamic list in Excel).

Now, the overall formula will be:

Here, for the second criteria, we are fixing all the Horizontal values. Press Enter to return the result.

Check the formula by selecting any other names from the dropdown list.

Example 4: Return Approximate Match Using XLOOKUP

Remember, we used bonus percentage data? That data was randomly made.

But what if we want to create a new bonus scheme?

We have our salary criteria and new bonus percentage ready on sheet 2.

According to the salary range, we want the approximate new bonus percentage.

Type in the following formula:

Here, we used -1 as the match_mode which will find the exact match or return the next smaller value.

Can’t Use XLOOKUP in Excel?

Xlookup is not available on the older versions of Microsoft Excel. You must have a Microsoft Office 365 account. The Xlookup function is not seen on Excel 2016 or 2019 versions.

You may see a handful of Excel 2019 versions using XLOOKUP because someone might have created this function using the newer versions.

Upgrade your Microsoft account to 365 by taking their yearly subscription (saves a few dollars) to unlock XLOOKUP and other newer functions in Excel.

How to use Xlookup in Excel Online

XLOOKUP is also available in the online version of Microsoft 365. If you have an account (educational or business), you can easily use these new and advanced functions.

Here are the steps to use Xlookup in Excel Online:

1. Select an empty column and type =XLOOKUP.
2. Determine which value to find in the first argument.
3. Select the range and return values in the second and third arguments.
4. Check whether you want exact matches or not. If not, then use the fourth argument.

FAQ

Q: Can XLOOKUP have two lookup values?

A: You can provide Excel for multiple lookup values depending on criteria. But do remember that XLOOKUP doesn’t work on texts. It works only on numbers. If you put any text format on any of the arguments, Excel will return the header name only.

Q: What is the advantage of Xlookup in Excel?

A: Xlookup is advantageous because it works as efficiently as VLOOKUP and INDEX-MATCH functions combined. You can also perform a two-way lookup value using the XLOOKUP.

Q: What does XLOOKUP contain?

A: Xlookuup is a versatile and powerful function. It contains 6 arguments, out of which the first 3 arguments are a must. Rest of the 3 arguments are optional and depend only on the criteria of the dataset. The syntax for Xlookup is- =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).

Q: Can you use Xlookup in a table?

A: Yes, you can use the XLOOKUP function in a table in Microsoft Excel. In fact, using XLOOKUP in a table can be very convenient because you can refer to the table columns by their names instead of their cell references.

Q: Can you use Xlookup for rows?

A: Yes, you can use Xlookup for rows. It’s a great alternative for HLOOKUP (Horizontal lookup).

Q: Does Xlookup only work on numbers?

A: Xlookup only works on numbers. It doesn’t work on texts. If you select an texts as a lookup_array or a return_array, the results will be inaccurate. Excel will return the text as the result.

Q: What is the character limit for Xlookup?

A: The XLOOKUP function in Microsoft Excel has a limit of 255 characters for the lookup_value, if_not_found, and search_mode arguments. This means that if you are using XLOOKUP to search for a value that is longer than 255 characters, you may end up with an error.

Conclusion

In this post, we described how to use the new XLOOKUP function in Excel more efficiently in the correct way.

There are more advanced functions like this one which can reduce the time of returning complex results.

Instead of complex combined functions, it’s easier to use one simple function to complete the whole process.