How to Use XLOOKUP in Excel
XLOOKUP Function Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if _not_found], [match_mode], [search_mode])
Parameter:
- lookup_value: The value which we want to search
- lookup_array: The range in which we want to search
- if_not_found (Optional): The text which we want to return if the value is not found
- match_mode (Optional): Here we can specify the type of match we want.
Match Type | Behavior |
---|---|
0 (default) | Here it will look for an exact match |
-1 | If we do not get the exact match then it will return the next smaller item. |
1 | If we do not get the exact match then it will return the next larger item. |
2 | It will do partial matching using (* or ~) |
- search_mode (Optional): where we can specify how the function should look up the value.
Search mode | Behavior |
---|---|
1(default) | It will search the values first. |
-1 | It will search the values in reverse from last. |
2 | It will perform a binary search and data needs to be sorted in ascending order. |
-2 | It will perform a binary search and data needs to be sorted in descending order. |
In simple language :
=XLOOKUP(search for this value, in this range, and Return a match from this range).
Below are some examples of the XLOOKUP.
XLOOKUP Function in Excel With Examples
XLOOKUP Function helps us to search value in a horizontal or vertical dataset and return the relative value in some other row or column. In this article, we will look at the XLOOKUP Function in Excel. XLOOKUP is a modern replacement for functions like HLOOKUP, VLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards for partial matches, and lookups in horizontal or vertical ranges. XLOOKUP can be performed in Microsoft 365.
Contact Us