The XLOOKUP function in Excel is a powerful tool for searching and retrieving data from a table or range. Below are 10 examples of how you can use XLOOKUP, including sample data and formulas.
Example 1: Basic XLOOKUP
Data:
A B 1 Apple 5 2 Banana 3 3 Orange 7
Formula:
=XLOOKUP("Banana", A1:A3, B1:B3)Result:
3
Example 2: XLOOKUP with Exact Match
Data:
A B 1 Apple 5 2 Banana 3 3 Orange 7
Formula:
=XLOOKUP("Orange", A1:A3, B1:B3, 0, 0)Result:
7
Example 3: XLOOKUP with Approximate Match
Data:
A B 1 10 $10 2 20 $20 3 30 $30
Formula:
=XLOOKUP(25, A1:A3, B1:B3, "Not Found", 1)
Result:
$20
Example 4: XLOOKUP with Wildcard
Data:
A B 1 Apple 5 2 Banana 3 3 Orange 7
Formula:
=XLOOKUP("App*", A1:A3, B1:B3)Result:
5
Example 5: XLOOKUP with Multiple Criteria
Data:
A B C 1 Apple Red 5 2 Banana Yellow 3 3 Orange Orange 7
Formula:
=XLOOKUP(1, (A1:A3="Apple")*(B1:B3="Red"), C1:C3)
Result:
5
Example 6: XLOOKUP with Return Array
Data:
A B C 1 Apple Red 5 2 Banana Yellow 3 3 Orange Orange 7
Formula:
=XLOOKUP("Banana", A1:A3, B1:C3)Result:
Yellow 3
Example 7: XLOOKUP with Reverse Search
Data:
A B 1 Apple 5 2 Banana 3 3 Orange 7
Formula:
=XLOOKUP("Banana", A1:A3, B1:B3, "Not Found", 0, -1)Result:
3
Example 8: XLOOKUP with Error Handling
Data:
A B 1 Apple 5 2 Banana 3 3 Orange 7
Formula:
=XLOOKUP("Grape", A1:A3, B1:B3, "Not Found")Result:
Not Found
Example 9: XLOOKUP with Dynamic Arrays
Data:
A B 1 Apple 5 2 Banana 3 3 Orange 7
Formula:
=XLOOKUP({"Apple", "Orange"}, A1:A3, B1:B3)Result:
5 7
Example 10: XLOOKUP with Spill Range
Data:
A B 1 Apple 5 2 Banana 3 3 Orange 7
Formula:
=XLOOKUP(A1:A3, A1:A3, B1:B3)
Result:
5 3 7
These examples demonstrate the versatility of the XLOOKUP function in Excel, showing how it can be used for various lookup scenarios, including exact and approximate matches, wildcards, multiple criteria, and error handling.
No comments:
Post a Comment