ntroduction: XLOOKUP is a new and powerful function in Excel that was introduced in Excel 365. It is similar to the VLOOKUP function, but it offers some additional and advanced features such as searching in any direction, exact or approximate matching, and returning multiple values. XLOOKUP stands for “extended lookup”, and it is a versatile and flexible function that can save you a lot of time and effort when working with large and complex data sets.
Syntax: The basic syntax of the XLOOKUP function is as follows:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value to search for in the lookup_array.
- lookup_array: The range of cells or table that contains the values to search in.
- return_array: The range of cells or table that contains the values to return. This range should have the same size as the lookup_array.
- if_not_found (optional): The value to return if the lookup_value is not found in the lookup_array. This can be a text or numeric value, or another function.
- match_mode (optional): The type of match to perform. This can be “0” for exact match, “-1” for exact or next smallest value, or “1” for exact or next largest value. The default is “1”.
- search_mode (optional): The direction of the search. This can be “1” for searching from the top to bottom, or “-1” for searching from the bottom to top. The default is “1”.
Example 1: Suppose you have a table of sales data for different products and regions, and you want to find the sales for a specific product in a specific region. The table looks like this:
| Product | Region | Sales |
|---|---|---|
| A | East | 100 |
| B | West | 200 |
| C | North | 300 |
| D | South | 400 |
To find the sales for product “B” in the “West” region, you can use the following XLOOKUP formula:
=XLOOKUP("B"&"West",A2:A5&B2:B5,C2:C5,0)Here, we concatenate the values of the Product and Region columns using the “&” operator to create a lookup_value that is unique for each cell. The lookup_array is the concatenated range A2:A5&B2:B5, and the return_array is the Sales column C2:C5. The match_mode is set to “0” for an exact match, and the search_mode is not specified, so it defaults to “1”. The formula returns the value “200”, which is the sales for product “B” in the “West” region.
Example 2: Suppose you have a table of employee data that contains the names, IDs, salaries, and departments of employees. You want to find the salary and department of an employee by their ID. The table looks like this:
| Name | ID | Salary | Department |
|---|---|---|---|
| John | 100 | 50000 | Sales |
| Mary | 101 | 60000 | Marketing |
| Bob | 102 | 55000 | IT |
| Alice | 103 | 65000 | Finance |
To find the salary and department of employee with ID “102”, you can use the following XLOOKUP formula:
=XLOOKUPHere is the full formula for Example 2:
=XLOOKUP(102,A2:A5,B2:C5,"Employee not found",0,-1)Here, the lookup_value is the ID “102”, the lookup_array is the ID column A2:A5, and the return_array is the Salary and Department columns B2:C5. The if_not_found value is set to “Employee not found” in case the ID is not found in the table. The match_mode is set to “0” for an exact match, and the search_mode is set to “-1” for searching from the bottom to top. The formula returns the values “55000” and “IT”, which are the salary and department of the employee with ID “102”.
Example 3: Suppose you have a table of customer data that contains the names, phone numbers, and email addresses of customers. You want to find the phone number and email address of a customer by their name, but you don’t know the exact spelling of their name. The table looks like this:
| Name | Phone | |
|---|---|---|
| John Smith | 123-456-7890 | john.smith@abc.com |
| Mary Johnson | 234-567-8901 | mary.johnson@abc.com |
| Bob Williams | 345-678-9012 | bob.williams@abc.com |
| Alice Lee | 456-789-0123 | alice.lee@abc.com |
To find the phone number and email address of a customer with a name that contains the text “john”, you can use the following XLOOKUP formula:
=XLOOKUP("*john*",A2:A5,C2:D5,"Customer not found",1,-1)Here, we use the wildcard character “*” to match any text that contains the letters “john”. The lookup_value is the text “john“, the lookup_array is the Name column A2:A5, and the return_array is the Phone and Email columns C2:D5. The if_not_found value is set to “Customer not found” in case no matching name is found in the table. The match_mode is set to “1” for an approximate match, and the search_mode is set to “-1” for searching from the bottom to top. The formula returns the values “123-456-7890” and “john.smith@abc.com“, which are the phone number and email address of the customer with the name “John Smith”.
Conclusion: XLOOKUP is a powerful and versatile function in Excel that can be used for a wide range of lookup and search tasks. It offers several advanced features such as searching in any direction, exact or approximate matching, and returning multiple values. By understanding the syntax and examples of XLOOKUP, you can save a lot of time and effort when working with large and complex data sets in Excel.