XLOOKUP is a powerful function in Excel that can be used to look up and retrieve data from a table. In this article, we will discuss 10 ways that XLOOKUP can be used in Excel, along with the data used and the result.
Example 1: Looking up a Value in a Table
Suppose you have a list of products and their prices. You want to look up the price of a particular product.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the price of product B, you can use the following formula:
=XLOOKUP(“B”,A2:A5,B2:B5)
This formula looks up the value “B” in the range A2:A5 and returns the corresponding value from the range B2:B5. The result will be as follows:
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
Example 2: Looking up a Value and Returning a Different Value
Suppose you have a list of products and their prices. You want to look up the price of a particular product, but you want to return a message if the product is not found.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the price of product E and return a message if the product is not found, you can use the following formula:
=XLOOKUP(“E”,A2:A5,B2:B5,”Product not found”)
This formula looks up the value “E” in the range A2:A5 and returns the corresponding value from the range B2:B5. If the value is not found, the formula returns the message “Product not found.” The result will be as follows:
| Result |
|---|
| Product not found |
Example 3: Looking up Values in Reverse Order
Suppose you have a list of products and their prices, but the products are listed in reverse order. You want to look up the price of product B.
| Product | Price |
|---|---|
| D | 40 |
| C | 30 |
| B | 20 |
| A | 10 |
To look up the price of product B, you can use the following formula:
=XLOOKUP(“B”,A2:A5,B2:B5)
This formula looks up the value “B” in the range A2:A5 and returns the corresponding value from the range B2:B5. The result will be as follows:
| Product | Price |
|---|---|
| D | 40 |
| C | 30 |
| B | 20 |
| A | 10 |
Example 4: Returning Multiple Values
Suppose you have a list of products and their prices, and you want to return the prices for multiple products at once.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To return the prices for products A and B, you can use the following formula:
=XLOOKUP({“A”,”B”},A2:A5,B2:B5)
This formula looks up the values “A” and “B” in the range A2:A5 and returns the corresponding values from the range B2:B5. The result will be as follows:
| Result |
|---|
| 10 |
| 20 |
Example 5: Returning the Nearest Value
Suppose you have a list of grades and you want to find the nearest grade to 70.
| Grade |
|---|
| 60 |
| 65 |
| 70 |
| 75 |
| 80 |
To find the nearest grade to 70, you can use the following formula:
=XLOOKUP(70,A2:A6,B2:B6,,1)
This formula looks up the value 70 in the range A2:A6 and returns the corresponding value from the range B2:B6. If the value is not found, the formula returns the nearest value. The result will be as follows:
| Result |
|---|
| 70 |
Example 6: Looking up Values in a Non-Contiguous Range
Suppose you have a list of products and their prices, and you want to look up the prices for a non-contiguous range of products.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the prices for products A and C, you can use the following formula:
=XLOOKUP({“A”,”C”},A2:A5,B2:B5)
This formula looks up the values “A” and “C” in the range A2:A5 and returns the corresponding values from the range B2:B5. The result will be as follows:
| Result |
|---|
| 10 |
| 30 |
Example 7: Looking up Values in a Vertical Range
Suppose you have a list of products and their prices, and you want to look up the prices in a vertical range.
| Product | Price 1 | Price 2 |
|---|---|---|
| A | 10 | 20 |
| B | 30 | 40 |
| C | 50 | 60 |
To look up the price of product B in the vertical range, you can use the following formula:
=XLOOKUP(“B”,A2:A4,B2:C4)
This formula looks up the value “B” in the range A2:A4 and returns the corresponding value from the range B2:C4. The result will be as follows:
| Product | Price 1 | Price 2 |
|---|---|---|
| B | 30 | 40 |
Example 8: Handling Errors
Suppose you have a list of products and their prices, and you want to handle errors when a value is not found.
| Product | Price |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
To look up the price of product E and handle the error, you can use the following formula:
=XLOOKUP(“E”,A2:A5,B2:B5,,,”Not found”)
This formula looks up the value “E” in the range A2:A5 and returns the corresponding value from the range B2:B5. If the value is not found, the formula returns the message “Not found.” The result will be as follows:
| Result |
|---|
| Not found |
Example 9: Looking up Values with Wildcards
Suppose you have a list of products and you want to look up the prices for products that have a name that contains the word “apple.”
| Product | Price |
|---|---|
| Red Apple | 10 |
| Green Apple | 20 |
| Orange | 30 |
| Grape | 40 |
To look up the prices for products that contain the word “apple,” you can use the following formula:
=XLOOKUP(“apple“,A2:A5,B2:B5)
This formula looks up the value “apple” in the range A2:A5 and returns the corresponding values from the range B2:B5. The result will be as follows:
| Result |
|---|
| 10 |
| 20 |
Example 10: Looking up Values with Multiple Criteria
Suppose you have a list of orders that includes the order number, the date the order was placed, and the salesperson who made the sale. You want to look up the order number for an order that was placed by John after March 1, 2023.
| Order Number | Order Date | Salesperson |
|---|---|---|
| 1001 | 3/2/2023 | John |
| 1002 | 3/3/2023 | Sarah |
| 1003 | 3/4/2023 | John |
| 1004 | 3/5/2023 | Tom |
| 1005 | 3/6/2023 | John |
To look up the order number for an order that was placed by John after March 1, 2023, you can use the following formula:
=XLOOKUP(1,(C2:C6=”John”)*(B2:B6>DATE(2023,3,1)),A2:A6)
This formula looks up the values 1 in the array (C2:C6=”John”)*(B2:B6>DATE(2023,3,1)) and returns the corresponding values from the range A2:A6. The result will be as follows:
| Result |
|---|
| 1003 |
Conclusion
XLOOKUP is a powerful function in Excel that can be used to look up and retrieve data from a table. By using XLOOKUP, you can perform a wide range of tasks in Excel, from looking up a single value to looking up values with multiple criteria. With these examples, you can get started with using XLOOKUP in your own Excel spreadsheets.
