Introduction
Excel is a widely used spreadsheet software that includes many powerful functions that help users work with data more quickly. This article will introduce a new Excel function – XLOOKUP, and how to use it.
What is the XLOOKUP function
XLOOKUP is a new Excel function that was first introduced in Excel 365. The XLOOKUP function is a lookup function that can help users look up a specific value in an Excel table and return the corresponding result. The XLOOKUP function has more flexibility and more features than other lookup functions.
What does the XLOOKUP function do
The XLOOKUP function is mainly used to look up and return values in a table. Using the XLOOKUP function, users can look up a value, a range, or multiple values in an array, and return results corresponding to those values. The XLOOKUP function also supports fuzzy and exact matching, and wildcards can be used for lookups. It can replace traditional lookup functions such as HLOOKUP and VLOOKUP.
XLOOKUP parameter details
The 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 look up
- lookup_array: range or array to look up
- return_array: range or array to return
- if_not_found: (optional) the value to return if no match is found
- match_mode: (optional) match mode (0 or 1), default is 1, means exact match
- search_mode: (optional) search mode (1 or -1), the default is 1, which means search from small to large
XLOOKUP example
Following are three use cases for the XLOOKUP function.
Case 1: exact match
Suppose we have a table with product names and corresponding prices. We want to find the price based on the product name.
| product name | price |
|---|---|
| Item 1 | 10.00 |
| Item 2 | 15.00 |
| Item 3 | 20.00 |
| Item 4 | 25.00 |
| Item 5 | 30.00 |
We can use the XLOOKUP function to do the lookup. Enter the following formula in the first cell:
=XLOOKUP("Item 3",A2:A6,B2:B6)This returns 20.00, indicating that item 3 is priced at 20.00.
Case 2: Fuzzy matching
Now suppose we have a table with names and grades of students. We want to find the name of the student with the highest grade.
| Name | Grade |
|---|---|
| Alice | 75 |
| Susan | 80 |
| Elaine | 85 |
| Bob | 90 |
| Alex | 95 |
We can use the XLOOKUP function for fuzzy matching. Enter the following formula in the first cell:
=XLOOKUP(MAX(B2:B6),B2:B6,A2:A6,,"-1")This will return Alex because she has the highest grade.
Case 3: Return multiple values
Finally, let’s look at how to use the XLOOKUP function to return multiple values. Suppose we have a table with product names and corresponding prices and sales volumes. We want to find the price and sales volume based on the product name.
| Product Name | Price | Sales Volume |
|---|---|---|
| Item 1 | 10.00 | 100 |
| Item 2 | 15.00 | 150 |
| Item 3 | 20.00 | 200 |
| Item 4 | 25.00 | 250 |
| Item 5 | 30.00 | 300 |
=XLOOKUP("Item 3",A2:A6,B2:C6)This returns 20.00 and 200, indicating that item 3 has a price of 20.00 and a quantity of 200 sold.
Conclusion
The XLOOKUP function is a new Excel function that can help users look up specific values in an Excel table and return corresponding results. It has more flexibility and more functions, and can replace traditional lookup functions such as HLOOKUP and VLOOKUP. This article introduces the syntax and parameters of the XLOOKUP function, as well as three use cases, to help users better understand and use the XLOOKUP function.