XLOOKUP is a powerful function in Excel that allows you to search for a specific value in a table and return a corresponding value from another column in the same table. It is a function introduced in Excel 365 and has quickly become one of the most popular functions among Excel users due to its ease of use and versatility.
In this tutorial, we will cover the following topics related to XLOOKUP in Excel:
- How to use XLOOKUP
- Examples of using XLOOKUP with tables
- Using XLOOKUP with approximate matches
- Using XLOOKUP with exact matches
- Handling errors with XLOOKUP
So, let’s get started!
1. How to use XLOOKUP
The basic syntax for the XLOOKUP function is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Here’s what each of these arguments means:
lookup_value: The value you are searching for in the lookup_array.lookup_array: The column or row where you want to search for the lookup_value.return_array: The column or row from which you want to return a value based on the match.if_not_found(optional): The value to return if a match is not found.match_mode(optional): The type of match to use. Can be set to 0 or -1 for exact matches, or 1 or 2 for approximate matches.search_mode(optional): Determines whether the lookup_array is sorted in ascending or descending order. Can be set to 1 or -1.
Note that the last three arguments are optional, but the first three are required. If you leave any of the optional arguments blank, XLOOKUP will use default values.
2. Examples of using XLOOKUP with tables
Let’s take a look at a few examples of how you can use XLOOKUP with tables.
Example 1: Simple Lookup
Suppose you have a table with two columns: one column containing names and another column containing phone numbers. You want to find the phone number for a specific name. Here’s how you can use XLOOKUP to do that:
Enter the name you want to search for in a cell (e.g., cell A1).
In another cell (e.g., cell B1), enter the following formula:
=XLOOKUP(A1,NameList,PhoneList)
In this formula, A1 is the lookup_value, NameList is the lookup_array, and PhoneList is the return_array.
The result will be the phone number associated with the name in cell A1.
Example 2: Reverse Lookup
Suppose you have a table with two columns: one column containing product IDs and another column containing product names. You want to find the name of a product based on its ID. Here’s how you can use XLOOKUP to do that:
Enter the product ID you want to search for in a cell (e.g., cell A1).
In another cell (e.g., cell B1), enter the following formula:
=XLOOKUP(A1,ProductIDList,ProductNameList)
In this formula, A1 is the lookup_value, ProductIDList is the lookup_array, and ProductNameList is the return_array.
The result will be the name of the product associated with the ID in cell A1.
Example 3: Multiple Criteria Lookup
Suppose you have a table with three columns: one column containing customer names, another column containing product names, and a third column containing sales data. You want to find the sales data for a specific customer and product combination. Here’s how you can use XLOOKUP to do that:
Enter the customer name you want to search for in a cell (e.g., cell A1).
Enter the product name you want to search for in another cell (e.g., cell B1).
In another cell (e.g., cell C1), enter the following formula:
=XLOOKUP(1,(CustomerList=A1)*(ProductList=B1),SalesDataList)
In this formula, 1 is a dummy value that XLOOKUP uses to perform the lookup.
The expression (CustomerList=A1)*(ProductList=B1) creates an array that contains 1s for each row where the customer name matches A1 and the product name matches B1, and 0s for all other rows.
The XLOOKUP function then uses this array as the lookup_array and returns the corresponding value from the SalesDataList.
Note that we are using an array formula here, so you will need to press Ctrl + Shift + Enter instead of just Enter to enter the formula.
The result will be the sales data for the specified customer and product combination.
3. Using XLOOKUP with approximate matches
XLOOKUP can also be used to perform approximate matches. In this case, the lookup_array must be sorted in ascending order.
Suppose you have a table with two columns: one column containing exam scores and another column containing letter grades. You want to find the letter grade that corresponds to a specific score. Here’s how you can use XLOOKUP to do that:
Enter the score you want to search for in a cell (e.g., cell A1).
In another cell (e.g., cell B1), enter the following formula:
=XLOOKUP(A1,ScoreList,GradeList,,"-1")
In this formula, A1 is the lookup_value, ScoreList is the lookup_array, and GradeList is the return_array.
We have also specified a match_mode of “-1” to indicate that we want to perform an approximate match.
The result will be the letter grade that corresponds to the specified score.
4. Using XLOOKUP with exact matches
If you want to perform an exact match, you can set the match_mode argument to “0” or “-1”. Here’s an example:
Suppose you have a table with two columns: one column containing employee IDs and another column containing employee names. You want to find the name of an employee based on their ID. Here’s how you can use XLOOKUP to do that:
Enter the employee ID you want to search for in a cell (e.g., cell A1).
In another cell (e.g., cell B1), enter the following formula:
=XLOOKUP(A1,EmployeeIDList,EmployeeNameList,,"0")
In this formula, A1 is the lookup_value, EmployeeIDList is the lookup_array, and EmployeeNameList is the return_array.
We have also specified a match_mode of “0” to indicate that we want to perform an exact match.
The result will be the name of the employee associated with the specified ID.
5. Handling errors with XLOOKUP
If XLOOKUP is unable to find a match, it will return an #N/A error. You can handle this error by using the IFERROR function. Here’s an example:
Suppose you have a table with two columns: one column containing product names and another column containing prices. You want to find the price of a specific product, but if the product is not found in the table, you want to display a custom message instead of the #N/A error. Here’s how you can use XLOOKUP and IFERROR to do that:
Enter the product name you want to search for in a cell (e.g., cell A1).
In another cell (e.g., cell B1), enter the following formula:
=IFERROR(XLOOKUP(A1,ProductList,PriceList),"Product not found")
In this formula, A1 is the lookup_value, ProductList is the lookup_array, and PriceList is the return_array.
The IFERROR function checks the result of the XLOOKUP function and returns the value from the XLOOKUP function if it is not an error, or the custom message “Product not found” if it is an error.
The result will be the price of the specified product, or the custom message if the product is not found.
6. Using XLOOKUP with wildcards
XLOOKUP also supports the use of wildcards in the lookup_value argument. This can be useful if you want to perform a partial match on a string. Here’s an example:
Suppose you have a table with two columns: one column containing product names and another column containing prices. You want to find the price of a product that contains a specific word in its name. Here’s how you can use XLOOKUP and wildcards to do that:
Enter the word you want to search for in a cell (e.g., cell A1).
In another cell (e.g., cell B1), enter the following formula:
=XLOOKUP(""&A1&"",ProductList,PriceList)
In this formula, “*” is a wildcard that matches any number of characters.
The ampersand (&) is used to concatenate the wildcard, the search term in A1, and another wildcard.
This creates a lookup_value that will match any product name that contains the search term.
The result will be the price of the first product that matches the search criteria.
Conclusion
XLOOKUP is a powerful function that can help you quickly find and retrieve data from tables in Excel. By using the examples and techniques covered in this tutorial, you can become proficient at using XLOOKUP in your own spreadsheets.