The XLOOKUP function in Excel is a powerful and versatile lookup function that allows you to search for a value in a range or array and return a corresponding value in another range or array. It is a more advanced and flexible alternative to the older VLOOKUP and HLOOKUP functions, and it can handle both vertical and horizontal lookups.
Syntax
| Function | Syntax |
|---|---|
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
- lookup_value: The value to search for.
- lookup_array: The array or range to search within.
- return_array: The array or range to return values from.
- [if_not_found]: Optional; the value to return if no match is found.
- [match_mode]: Optional; the match type (0 = exact match, -1 = exact match or next smaller, 1 = exact match or next larger, 2 = wildcard match).
- [search_mode]: Optional; the search mode (1 = search first-to-last, -1 = search last-to-first, 2 = binary search ascending, -2 = binary search descending).
Example
Consider the following table to illustrate the use of the XLOOKUP function:
| Product ID | Product Name | Price | Stock |
|---|---|---|---|
| 101 | Apple | 1.00 | 150 |
| 102 | Banana | 0.50 | 200 |
| 103 | Cherry | 2.00 | 50 |
| 104 | Date | 3.00 | 100 |
| 105 | Elderberry | 4.00 | 30 |
Steps:
- To find the price of the product with Product ID "103", use the formula:
=XLOOKUP(103, A2:A6, C2:C6). - To find the stock of the product named "Banana", use the formula:
=XLOOKUP("Banana", B2:B6, D2:D6).
Practice Exercise 1
Task: Using the table below, find the "Price" of the product named "Table".
Practice Exercise 2
Task: Using the table below, find the "Grade" of the student with ID "S102".
Exercise 1 Solution
To find the "Price" of the product named "Table":
Use the formula: =XLOOKUP("Table", B2:B6, C2:C6), which results in 85.00.
Exercise 2 Solution
To find the "Grade" of the student with ID "S102":
Use the formula: =XLOOKUP("S102", A2:A6, C2:C6), which results in B.
By mastering the XLOOKUP function in Excel, you can efficiently search for values and retrieve corresponding data, enhancing your data analysis and reporting capabilities.