The XLOOKUP function in Excel is a powerful tool for searching and retrieving data from a table or range. It replaces older functions like VLOOKUP, HLOOKUP, and INDEX/MATCH with more flexibility and simplicity. Below are 20 examples of how to use the XLOOKUP function:
Basic Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value: The value you want to search for.
lookup_array: The range or array to search in.
return_array: The range or array to return a value from.
[if_not_found]: (Optional) What to return if no match is found.
[match_mode]: (Optional) Specify the type of match (exact, wildcard, etc.).
[search_mode]: (Optional) Specify the search direction (first-to-last, last-to-first, etc.).
Examples
1. Basic Exact Match
Look up the price of a product by its ID.
=XLOOKUP("A123", A2:A10, B2:B10)Searches for "A123" in
A2:A10and returns the corresponding value fromB2:B10.
2. Handle Not Found
Return "Not Found" if the lookup value doesn't exist.
=XLOOKUP("XYZ", A2:A10, B2:B10, "Not Found")3. Case-Insensitive Match
Look up a value without case sensitivity.
=XLOOKUP("apple", A2:A10, B2:B10, , 0)4. Wildcard Match
Use wildcards (* or ?) for partial matches.
=XLOOKUP("app*", A2:A10, B2:B10)Finds the first value in
A2:A10that starts with "app".
5. Search from Bottom to Top
Search the lookup array from the last item to the first.
=XLOOKUP("A123", A2:A10, B2:B10, , , -1)6. Return Multiple Columns
Return values from multiple columns.
=XLOOKUP("A123", A2:A10, B2:D10)Returns the entire row from columns B to D.
7. Lookup with Multiple Criteria
Use concatenation or arrays for multiple criteria.
=XLOOKUP(1, (A2:A10="Category1")*(B2:B10="Subcategory1"), C2:C10)
8. Approximate Match
Find the closest match for a numeric value.
=XLOOKUP(85, A2:A10, B2:B10, , 1)
Returns the closest value less than or equal to 85.
9. Reverse Lookup
Look up a value in a column and return a value from a row.
=XLOOKUP("A123", A2:A10, B1:D1)10. Dynamic Array Return
Return multiple values using dynamic arrays.
=XLOOKUP("A123", A2:A10, B2:D10)Returns all matching rows in columns B to D.
11. Lookup with Dates
Find a value based on a date.
=XLOOKUP(DATE(2023,10,15), A2:A10, B2:B10)
12. Nested XLOOKUP
Use XLOOKUP inside another XLOOKUP.
=XLOOKUP("A123", A2:A10, XLOOKUP("Category1", B2:B10, C2:C10))13. Lookup with Logical Operators
Use logical operators in the lookup array.
=XLOOKUP(TRUE, A2:A10>50, B2:B10)
Returns the first value in
B2:B10whereA2:A10is greater than 50.
14. Lookup with Array Input
Look up multiple values at once.
=XLOOKUP({"A123","B456"}, A2:A10, B2:B10)Returns an array of results for "A123" and "B456".
15. Lookup with Spill Range
Return results that spill into adjacent cells.
=XLOOKUP("A123", A2:A10, B2:D10)Spills results into multiple cells.
16. Lookup with Error Handling
Handle errors gracefully.
=XLOOKUP("A123", A2:A10, B2:B10, IFERROR("Not Found", "Error"))17. Lookup with Array Formula
Use XLOOKUP in an array formula.
=XLOOKUP(A2:A10, D2:D10, E2:E10)
Returns an array of results for each value in
A2:A10.
18. Lookup with Conditional Formatting
Highlight cells based on XLOOKUP results.
=XLOOKUP(A2, A2:A10, B2:B10) > 100
Use this in conditional formatting to highlight cells where the result is greater than 100.
19. Lookup with Text Functions
Combine XLOOKUP with text functions like LEFT or RIGHT.
=XLOOKUP(LEFT(A2, 3), A2:A10, B2:B10)
Looks up the first 3 characters of
A2inA2:A10.
20. Lookup with Dynamic Ranges
Use XLOOKUP with dynamic named ranges.
=XLOOKUP("A123", MyDynamicRange, B2:B10)MyDynamicRangeis a named range that adjusts automatically.
These examples demonstrate the versatility of XLOOKUP in Excel. You can adapt these formulas to suit your specific data and requirements!
Sure! Let’s go through 5 practical examples of the XLOOKUP function with sample data included. This will help you understand how to apply XLOOKUP in real-world scenarios.
Example 1: Basic Exact Match
Task: Look up the price of a product based on its ID.
| Product ID | Product Name | Price |
|---|---|---|
| A123 | Apple | 1.20 |
| B456 | Banana | 0.50 |
| C789 | Cherry | 2.00 |
Formula:
=XLOOKUP("B456", A2:A4, C2:C4)Result: 0.50 (the price of Banana).
Example 2: Handle Not Found
Task: Look up the price of a product, but return "Not Found" if the product doesn’t exist.
| Product ID | Product Name | Price |
|---|---|---|
| A123 | Apple | 1.20 |
| B456 | Banana | 0.50 |
| C789 | Cherry | 2.00 |
Formula:
=XLOOKUP("XYZ", A2:A4, C2:C4, "Not Found")Result: Not Found (since "XYZ" doesn’t exist in the list).
Example 3: Wildcard Match
Task: Find the price of a product whose name starts with "App".
| Product ID | Product Name | Price |
|---|---|---|
| A123 | Apple | 1.20 |
| B456 | Banana | 0.50 |
| C789 | Cherry | 2.00 |
Formula:
=XLOOKUP("App*", B2:B4, C2:C4)Result: 1.20 (the price of Apple).
Example 4: Approximate Match
Task: Find the discount percentage for a given purchase amount.
| Purchase Amount | Discount % |
|---|---|
| 0 | 0% |
| 100 | 5% |
| 500 | 10% |
| 1000 | 15% |
Formula:
=XLOOKUP(750, A2:A5, B2:B5, , 1)
Result: 10% (the closest match less than or equal to 750).
Example 5: Return Multiple Columns
Task: Retrieve the product name and price for a given product ID.
| Product ID | Product Name | Price |
|---|---|---|
| A123 | Apple | 1.20 |
| B456 | Banana | 0.50 |
| C789 | Cherry | 2.00 |
Formula:
=XLOOKUP("C789", A2:A4, B2:C4)Result: Cherry and 2.00 (the product name and price for "C789").
Example 6: Lookup with Multiple Criteria
Task: Find the price of a product in a specific category.
| Category | Product Name | Price |
|---|---|---|
| Fruit | Apple | 1.20 |
| Fruit | Banana | 0.50 |
| Vegetable | Carrot | 0.80 |
Formula:
=XLOOKUP(1, (A2:A4="Fruit")*(B2:B4="Banana"), C2:C4)
Result: 0.50 (the price of Banana in the Fruit category).
Example 7: Lookup with Dates
Task: Find the sales amount for a specific date.
| Date | Sales Amount |
|---|---|
| 2023-10-01 | 500 |
| 2023-10-02 | 700 |
| 2023-10-03 | 600 |
Formula:
=XLOOKUP(DATE(2023,10,2), A2:A4, B2:B4)
Result: 700 (the sales amount for October 2, 2023).
Example 8: Lookup with Spill Range
Task: Retrieve all details for a specific product ID.
| Product ID | Product Name | Price | Category |
|---|---|---|---|
| A123 | Apple | 1.20 | Fruit |
| B456 | Banana | 0.50 | Fruit |
| C789 | Cherry | 2.00 | Fruit |
Formula:
=XLOOKUP("B456", A2:A4, B2:D4)Result: Banana, 0.50, and Fruit (all details for "B456").
Example 9: Lookup with Array Input
Task: Look up prices for multiple product IDs at once.
| Product ID | Product Name | Price |
|---|---|---|
| A123 | Apple | 1.20 |
| B456 | Banana | 0.50 |
| C789 | Cherry | 2.00 |
Formula:
=XLOOKUP({"A123","C789"}, A2:A4, C2:C4)Result: 1.20 and 2.00 (prices for "A123" and "C789").
Example 10: Lookup with Dynamic Ranges
Task: Look up the price of a product using a dynamic named range.
| Product ID | Product Name | Price |
|---|---|---|
| A123 | Apple | 1.20 |
| B456 | Banana | 0.50 |
| C789 | Cherry | 2.00 |
Steps:
Create a named range (e.g.,
Products) forA2:A4.Use the formula:
=XLOOKUP("B456", Products, C2:C4)Result: 0.50 (the price of Banana).
These examples with data should help you understand how to use XLOOKUP effectively in Excel
No comments:
Post a Comment