where code (B5:B15) and quantity (C5:C15) are named ranges. In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear anywhere in the lookup values in B5:B15. The formula in F5 is:
lookup_value - E5, with asterisks (*) concatenated front and back lookup_array - the named range code (B5:B15) return_array - the named range quantity (C5:C15) if_not_found - the string “no match” match_mode - provided as 2 (wildcard match) search_mode - not provided. Defaults to 1 (first to last)
To make a “contains” type match automatic, the wildcard asterisk (*) is both prepended and appended to the value in cell E5 with concatenation: After concatenation, the formula becomes: XLOOKUP locates the first match that contains “BCC” (050-BCC-123 in row 10) and returns the corresponding value from the return array, 50. Note that XLOOKUP is not case-sensitive, entering “bcc” in E5 will return the same result: See below for an option to configure XLOOKUP for a case-sensitive match.
VLOOKUP option
The VLOOKUP formula also supports wildcards when set to exact match. The equivalent VLOOKUP formula for this example is: Full explanation here.
With SEARCH and FIND
It is also possible to use the SEARCH and FIND functions to perform a “contains” type match with XLOOKUP. For a case-insensitive match (like the example above), you can use SEARCH like this: For a case-sensitive match, you can use FIND instead: Both options above make it easier to extend criteria to include other conditions using boolean logic. The logic for ISNUMBER + SEARCH is explained here.
Multiple matches
If you need multiple matches, see the FILTER function.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
