What is XLOOKUP and how to use it in Excel?

XLOOKUP is like a super-powered search function in Excel. Imagine you have a big list of names and phone numbers, but you only want to find one specific phone number. You could use XLOOKUP to search through the list and find that phone number quickly.

‍

Here's how it works: You tell XLOOKUP what you're looking for (like a name or a number), and where to look for it (like a table or range of cells). Then, XLOOKUP goes through that list and finds the exact match you're looking for.

‍

It's a really useful tool for working with big sets of data in Excel, because it can help you find the information you need much faster than manually searching through a list.

‍

XLOOKUP vs VLOOKUP

‍

XLOOKUP is a newer function in Excel that was introduced in 2019, while VLOOKUP has been around for a long time. Both functions allow you to search for data in a table and return related information, but there are some key differences between them.

‍

The main advantage of XLOOKUP over VLOOKUP is that it is more flexible and powerful. Here are some of the key differences:

‍

1. Lookup direction: VLOOKUP can only search for a value in the first column of a table and return a related value in a subsequent column. XLOOKUP can search for a value in any column of a table and return a related value from any other column.
2. Return values: VLOOKUP can only return one value from the table, while XLOOKUP can return multiple values from multiple columns of the table.
3. Range lookup: VLOOKUP has an optional argument called "range lookup" which can be confusing and lead to errors. XLOOKUP does not have this argument and instead allows you to specify the search mode directly.
4. Column indexing: VLOOKUP requires you to use column index numbers to return a value from a specific column of a table. XLOOKUP allows you to use column names, which is easier to understand and use.

‍

Syntax of XLOOKUP

‍

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

‍

Let’s see what each argument means:

‍

lookup_value: refers to the value that you want to search for in a range or array

lookup_array: refers to the range or array where you want to search for the lookup_value

return_array: refers to the range or array that contains the values that you want to return

‍

[[if_not_found]: is an optional parameter that specifies what should be returned if XLOOKUP cannot find a match for the lookup_value in the lookup_array

‍

[search_mode]: is an optional parameter that specifies how XLOOKUP should perform the search for the lookup_value in the lookup_array

‍

[match_mode]: is an optional parameter that specifies how XLOOKUP should match the lookup_value with the values in the lookup_array

‍

Example

‍

Let’s understand XLOOKUP with an example.

‍

Let’s say, we have to find the employee with the employee ID 5124 from the following table. In this case, VLOOKUP doesn’t work as the Lookup Array is on the right and VLOOKUP expects the Lookup Array to be on the left. Let’s see how we can use XLOOKUP here.

‍

‍

Here, you need to use XLOOKUP, provide column C as the lookup_array and column A as the return_array. If you are doing it on the third sheet while the table is on the first sheet named ‘Data’, the formula would look like it’s there in the following screenshot and you will get the name of the employee.

‍

‍

This is one practical use case of XLOOKUP. If you want to learn about the other arguments of XLOOKUP Syntax and learn XLOOUP in-detail with different examples, the following video is for you.

‍