Summary

To perform a partial (wildcard) match against numbers, you can use an array formula based on the MATCH function and the TEXT function. In the example shown, the formula in E6 is:

=MATCH("*"&E5&"*",TEXT(data,"0"),0)

where data is the named range B5:B15. The result is 7 since the number in B11 (the seventh row in data) contains 99.

This is an array formula and must be entered with Control + Shift + Enter, except in Excel 365.

Generic formula

{=MATCH("*"&number&"*",TEXT(range,"0"),0)}

Explanation 

Excel supports the wildcard characters "*" and "?", and these wildcards can be used to perform partial (substring) matches in various lookup formulas. However, if you use wildcards with a number, you'll convert the numeric value to a text value. In other words, "*"&99&"*" = "*99*" (a text string), and if you try to find a text value in a range of numbers, the match will fail.

One solution is to convert the numeric values to text with the TEXT function like this:

=MATCH("*"&E5&"*",TEXT(data,"0"),0)

This is an array formula and must be entered with Control + Shift + Enter, except in Excel 365.

This formula uses the TEXT function to transform the numbers in B5:B10 to text with the number format "0". Because we give the entire range to TEXT, we get back all values converted to text in an array, which is returned directly to the MATCH function as the array argument. With the numbers converted to text, the MATCH function can find a partial match as usual.

Note that MATCH must be configured for exact match to use wildcards, by setting the 3rd argument to zero (0) or FALSE.

Another option

Another way to transform a number to text is to concatenate the numbers to an empty string ("") with the ampersand (&) operator like this:

=MATCH("*"&E5&"*",data&"",0)

The numbers in data become text without formatting, and the result is the same as above, 7.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.