XLOOKUP is a lookup function that is used to find things in a table within a column. Some examples could be finding an employee’s department with the use of their department code, finding the product name from its product ID, and finding the customer’s names that purchased from your shop with the use of the customer_id. It is extremely useful in Excel whenever you have to join specific columns from different datasets together.
This post will focus on teaching you how to use XLOOKUP by breaking down its formula and will do a simple example for illustration purposes. Let’s begin!
XLOOKUP Formula
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The XLOOKUP formula can be found above, with 3 COMPULSORY inputs and 3 NON-COMPULSORY inputs. We will be diving deeper into each input to better understand what is to be included in them.
lookup_value
the lookup_value will be a single cell that will contain the key that you want to look for in another data table. This lookup_value is a key that is found in the main table. It usually has no significant value on its own but provides key insights if mapped to the correct data table. This is a compulsory input for XLOOKUP to work.
lookup_array
the lookup_array will be a column that we will be using to search for whatever is stated in the lookup_value. The lookup_array is usually found in a separate data table, which will consist of columns that relate to the lookup_values (aka the keys), and the descriptions tagged to the keys. Some examples of columns that act as lookup_array would be customer_id, department_id, product_id, etc. This is a compulsory input for XLOOKUP to work.
return_array
the return_array will be the column that consists of the output we want to return. The return_array output is found in the same table as the lookup_array since you will be using the lookup_array to retrieve the return_array output. Some examples of columns that will act as return_array would be Department Name, Product Name, Customer Name, etc. This is a compulsory input for XLOOKUP to work.
[if_not_found]
This input allows you to return any output specified in the event that the lookup_value cannot find a matching key in the lookup_array. This can be any output, ranging from integers, text, blank values, and even specified cells in your Excel sheet. If nothing is specified, then #N/A will be returned. This is NOT A COMPULSORY INPUT for XLOOKUP to work.
[match_mode]
This specifies how you want the lookup_value to be mapped to the lookup_array. There are 4 different kinds of matching modes, and they are as follows:
[match_mode] | Match Type |
0 | lookup_value and lookup_array must be an EXACT MATCH. Otherwise, it will return the output specified in [if not found] |
-1 | lookup_value and lookup_array must be an EXACT MATCH. Otherwise, it will return the next smallest item |
1 | lookup_value and lookup_array must be an EXACT MATCH. Otherwise, it will return the next larger item |
2 | A wildcard match (to understand more about wildcards, click here) |
This is NOT A COMPULSORY INPUT for XLOOKUP to work. If you do not want to implement [match_mode], you can leave this blank
[search_mode]
This specifies how you want the lookup_value to search the lookup_array. There are 4 different kinds of searching modes that can be applied to the lookup_value. They are as follows:
[search_mode] | Match Type |
1 | lookup_value will start searching the lookup_array from the start of the column to the end of the column |
-1 | lookup_value will start searching the lookup_array from the end of the column to the start of the column |
2 | lookup_value will perform a binary search that relies on lookup_array being sorted in ascending order. |
-2 | lookup_value will perform a binary search that relies on lookup_array being sorted in descending order. |
This is NOT A COMPULSORY INPUT for XLOOKUP to work. If you do not want to implement [search_mode], you can leave this blank
XLOOKUP Example
Your boss gives you an employee’s table and wants you to find out the departments that each employee is in. He’s also given you a department table that contains the columns, department_id and department_name. How would you complete your boss asks?


To join these two tables together, we can use XLOOKUP! The formula will be as follows:

Let’s break down the formula that we’ve written down in the Excel sheet. The formula for XLOOKUP is:
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
–
To apply it in our context, it will be like:
=XLOOKUP(C2,G:G,H:H,”Department Not Found”,0, 1)
Because column C is the column that contains the department ID, this will be the lookup_value to be searched from the department’s table.
–
As there is a column called department_id in the department’s table, this will be the lookup_array for the lookup_value to search on.
–
As there is a column called department_name in the department’s table, we can return this output as the return_array if there is a match between the lookup_value and the lookup_array.
–
In the case where there is no match between the lookup_value and lookup_array, the formula will return an output of “Department Not Found”, to flag out items that are not accurately flagged. if we do not want to return anything, we can leave it blank
–
We need an EXACT MATCH in the as any lookup_value not found in the lookup_array means that there is no correct department name. This is why we need an EXACT MATCH, AND RETURN DEPARTMENT NOT FOUND IF THER IS NO MATCH
–
Because you are in a good mood, you decided to start from the start of the column, and end at the bottom. Thus, you selected 1.
–
This will give us the following output

Now, we can apply it to the other rows, and employees will all be mapped to their respective department names based on their department_id.

Personal Thoughts
As a data analyst, doing LOOKUP functions is very common, since joining tables in Excel is part of our day-to-day activity. XLOOKUP allows you to join specific columns from different data tables effectively, allowing you to create the table required to prepare your insights analysis.
What do you think of XLOOKUP? Do you use XLOOKUP in your day-to-day tasks? Let me know in the comments section below.