Summary
The Excel XLOOKUP function is a modern, powerful alternative for older lookup functions such as VLOOKUP, HLOOKUP, and LOOKUP. It offers more flexibility and functionality, supporting approximate and exact matching, wildcards for partial matches, and lookups in both vertical and horizontal ranges.
Syntax
=XLOOKUP(lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])
- lookup: The value to look for
- lookup_array: The array or range to search in
- return_array: The array or range to return values from
- [not_found]: [Optional] Value to return if the lookup value is not found
- [match_mode]: [Optional] Specifies the type of match: 0 for exact match (default), -1 for exact match or next smallest, 1 for exact match or next larger, 2 for wildcard match
- [search_mode]: [Optional] Specifies the search mode: 1 for normal (default), -1 for reverse, 2 for binary search ascending, -2 for binary search descending
Return value
The matching value from the return array.
How to use
XLOOKUP is used to find values within a specified range or array. It can handle both vertical and horizontal data and supports various matching and search modes.
Examples
Simple XLOOKUP
Finding Exact Matches: To find a specific value in a column and return a corresponding value:
=XLOOKUP(H4, B5:B9, E5:E9, "Not found")
This formula looks up the value in H4 within B5:B9 and returns the corresponding value from E5:E9. If not found, it returns “Not found”.
XLOOKUP for Approximate Match
Performing an Approximate Match: To calculate a discount based on quantity (approximate match):
=XLOOKUP(E5, B5:B9, C5:C9, "No match", -1)
This formula finds the closest match to E5 in B5:B9 and returns the corresponding value from C5:C9.
XLOOKUP for Multiple Value Return
Returning Multiple Values: Retrieve multiple related values for a single match:
=XLOOKUP(B5, B8:B15, C8:E15)
This returns the values from columns C to E for the matching value in B5.
XLOOKUP for Two-Way Lookup
Performing a Two-Way Lookup: Combining two XLOOKUPs for a matrix lookup:
=XLOOKUP(I6, C4:F4, XLOOKUP(I5, B5:B9, C5:F9))
This nested XLOOKUP first finds the row, then the column, returning the intersecting value.
Handling Not Found with XLOOKUP
Customizing ‘Not Found’ Message: Customizing the message when no match is found:
=XLOOKUP(H4, B5:B9, E5:E9, "Movie not found")
This looks up H4 in B5:B9 and returns a corresponding value from E5:E9, displaying “Movie not found” if no match is found.
XLOOKUP for Wildcard Match
Using Wildcards for Partial Matches: To find a value containing a specific substring:
=XLOOKUP("*" & G4 & "*", B5:B9, C5:C9, "No match", 2)
This uses wildcards to find a partial match of G4 in B5:B9 and returns the corresponding value from C5:C9.
Additional Notes
- XLOOKUP offers significant improvements over VLOOKUP, including the ability to search to the left, default exact matching, and more flexible search capabilities.
- The function is dynamic and updates automatically if the source data changes.
- XLOOKUP can perform reverse searches and handle entire rows or columns, not just single values.