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:A10
and 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:A10
that 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:B10
whereA2:A10
is 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
A2
inA2:A10
.
20. Lookup with Dynamic Ranges
Use XLOOKUP
with dynamic named ranges.
=XLOOKUP("A123", MyDynamicRange, B2:B10)
MyDynamicRange
is 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