Explanation

The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2.

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:

=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
  • 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 :

"*"&E5&"*"

After concatenation, the formula becomes:

=XLOOKUP("*BCC*",code,quantity,"no match",2)

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:

=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50

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:

=VLOOKUP("*"&E5&"*",B5:C15,2,0)

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:

=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)

=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)

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 .

Dynamic Array Formulas are available in Office 365 only.

Explanation

This formula uses XLOOKUP twice, by nesting one XLOOKUP inside another. The first (inner) XLOOKUP is used to perform an exact match lookup on the value in G5:

XLOOKUP(G5,E5:E15,B5:E15)
  • The lookup_value comes from cell G5
  • The lookup_array is E5:E15 (codes)
  • The return_array is B5:E15 (all fields)
  • The match_mode is not provided and defaults to 1 (exact match)
  • The search_mode is not provided and defaults to 1 (first to last)

The result is a match on “AX-160”, returned as an array of all four fields in the original order:

{160,130,60,"AX-160"}

This result is delivered directly to the second (outer) XLOOKUP as the return array argument. The lookup value is provided as a range representing the new order of fields, and the lookup array is the range containing the original field name order.

=XLOOKUP(G8:J8,B4:E4,{160,130,60,"AX-160"})
  • The lookup_value is the range G8:J8 (new field order)
  • The lookup_array is the range B4:E4 (old field order)
  • The return_array is the result from the first XLOOKUP

This is the tricky bit. We are passing in multiple lookup values, so XLOOKUP internally will calculate multiple match positions. For each value in the new field order range , XLOOKUP will find a position inside the old field order range and use this position to fetch a value from the return array (the values returned by the first XLOOKUP function). The result is the original lookup result with fields arranged in the new order.