It is a flexible and versatile function that can solve a wide variety of lookup problems.
The formula in H5 is:
More detailed explanation here.
The example below shows how XLOOKUP can be used to return three values with a single formula.
All three values are returned andspillinto the range C5:E5.
In the example above, we use XLOOKUP to return multiple values from the same matching record.
Example #4 - Two-way lookup
XLOOKUP can perform a two-way lookup bynestingone XLOOKUP inside another.
Typical values fornot_foundinclude “Not found”, “No match”, “No result”, etc.
Example #6 - Wildcard match
XLOOKUP supports wildcards to enable partial match lookups.
Set thematch_modeargument to 2 to enable wildcards in XLOOKUP.
For a slightly simpler formula,see this page.
This problem is trickier than it looks.
Each product code begins with 3 uppercase letters and ends with 2 or 3 uppercase letters.
In the middle of the product code is a number between 2 and 4 digits.
A wildcard match won’t work because a number like 56 can appear insideother product codes.
However, we can easily solve this problem with a “regex match”.
To enable a regex match in XLOOKUP, provide 3 formatch_mode.
Then supply a valid regex pattern as the lookup_value.
Regex is a powerful and somewhat complex language.
For a detailed explanation of this particular example (including the workbook),see this page.
Regex is a complicated and deep topic.
For an introduction to regex in Excel, seeRegular Expressions in Excel.
Regex support in XLOOKUP is only available in Excel 365.
Example #8 - multiple criteria
One common challenge with XLOOKUP is how to apply multiple criteria.
This sounds complicated, but it is quite straightforward.
Notice the sixth value in the array is 1.
This corresponds to the sixth row in the data, which contains a Medium Blue Hoodie.
For more details and to download the worksheet,see this page.
The formula in G5 looks like this:
Seethis pagefor a more detailed explanation of how this formula works.
To use binary search mode, data must be sorted in ascending or descending order.
If values are sorted inascending order, use the value 2 forsearch_mode.
If values are sorted indescending order, use the value -2.
Match mode options
By default, XLOOKUP will perform anexact match.
TheXMATCH functionwas also updated to support regex.
For more about regex in Excel, seeRegular Expressions in Excel.
Search mode options
By default, XLOOKUP will start matching from the first data value.
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
you might use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
LOOKUP’s default behavior makes it useful for solving certain problems in Excel.
HLOOKUP Function
The Excel HLOOKUP function finds and retrieve a value from data in a horizontal table.
HLOOKUP supports approximate and exact matching, and…
