XLOOKUP match text contains

To use XLOOKUP to match values that contain specific text, you can use wildcards and concatenation. In the example shown, the formula in F5 is: =XLOOKUP("*"&E5&"*",code,quantity,"no match",2) where code (B5:B15) and quantity (C5:C15) are named ranges.

April 14, 2025 · 1 min · 68 words · Gregory Davis

Table of Contents

VLOOKUP option

The VLOOKUP formula also supports wildcards when set to exact match.

The equivalent VLOOKUP formula for this example is:

Fullexplanation here.

The logic for ISNUMBER + SEARCH isexplained here.

Excel formula: XLOOKUP with logical criteria

Multiple matches

If you need multiple matches, see theFILTER function.

XLOOKUP supports approximate and exact matching, wildcards (* ?)

SEARCH returns the position offind_textinsidewithin_textas a number.

Excel formula: XLOOKUP with multiple criteria

When the text is not found, FIND returns a #VALUE error.

Excel formula: XLOOKUP case-sensitive

Excel XLOOKUP function

Excel SEARCH function

Excel FIND function

Excel ISNUMBER function