How to Use the XLOOKUP Function in Excel
Today, let’s dive into the capabilities of a powerful Excel and Google Sheets function known as XLOOKUP. XLOOKUP is designed to search through a range or array and returns the corresponding item from the first match it finds. This function serves as a modern replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP, providing greater flexibility and user-friendliness.
Basic Syntax
The fundamental syntax of the XLOOKUP function is:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Here’s a detailed explanation of the parameters:
- lookup_value: The value you are searching for.
- lookup_array: The array or range to perform the search in.
- return_array: The array or range from which to retrieve the result.
- if_not_found (optional): Specifies a value to return if the search does not find a match.
- match_mode (optional): This can be set to 0 for an exact match, -1 for an exact match or the next smaller item, or 1 for an exact match or the next larger item.
- search_mode (optional): Designates the search order; 1 for searching from first to last, -1 for last to first, or 2 for a binary search.
Example 1: Simple XLOOKUP
Consider a list of employees and their respective salaries. We need to find the salary of an employee named John using XLOOKUP.
| Employee | Salary |
|---|---|
| John | 50000 |
| Alice | 60000 |
| Bob | 45000 |
Formula: =XLOOKUP("John", A2:A4, B2:B4)
This formula successfully returns John’s salary as $50,000.
Example 2: XLOOKUP with Default Value
If a search doesn’t yield a result, you can set a default return value. Let’s update our previous example for an employee named David:
Formula: =XLOOKUP("David", A2:A4, B2:B4, "Not found")
This formula will return “Not found” as David is not listed among the employees.
Example 3: Using XLOOKUP with Match Modes
You can fine-tune the behavior of your XLOOKUP query with match modes. Suppose we want to find the closest salary to a certain amount not present in our array:
Formula: =XLOOKUP(55000, B2:B4, A2:A4, , 1)
This query returns “Alice” because $55,000 falls between John’s and Alice’s salaries, making hers the next higher match.
Conclusion
XLOOKUP greatly simplifies the process of searching for and retrieving data in Excel and Google Sheets. With its flexible and powerful options, it can accommodate a variety of lookup scenarios with ease, significantly enhancing data manipulation and retrieval tasks.
More information: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929