Exceljet

Quick, clean, and to the point

Partial match against numbers with wildcard

Excel formula: Partial match against numbers with wildcard
Generic formula 
{=MATCH("*"&number&"*",TEXT(range,"0"),0)}
Summary 

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

{=MATCH("*"&E5&"*",TEXT(B5:B10,"0"),0)}

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

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 together 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 inside a formula using the TEXT function or by concatenating and empty string to the range. To do this, the formula in E6 is:

{=MATCH("*"&E5&"*",TEXT(B5:B10,"0"),0)}

This is an array formula and must be entered with Control + Shift + Enter

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 or FALSE.

Another option

Another way to to transform a number to text is to concatenate an empty string (""). This formula works the same as the formula above:

=MATCH("*"&E5&"*",B5:B10&"",0)
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.