Partial match against numbers with wildcard

Partial match against numbers with wildcard

Generic formula 

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

Related formulas 

Exact match lookup with INDEX and MATCH

Explanation

To perform a partial match (a substring match) against numbers, you can use an array formula based on MATCH and TEXT.

Background

Excel supports the wildcard characters "*" and "?". 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).

If try to find a text value in a range of numbers, the match will fail.

Solution

One solution is to convert the numbers in the lookup range to text values, and then do a normal lookup with MATCH, VLOOKUP, etc. 

If this isn't practical, you can convert the numeric values to text inside a formula using the TEXT function or by concatenating and empty string to the range.

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 tranform the numbers in B5:B10 to text. Once the numbers are 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 tranform a number to text is to concatenate an empty string. This formula works the same as the formula above:

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

Partial match against numbers with wildcard

Generic formula 

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

Related formulas 

Exact match lookup with INDEX and MATCH

Explanation

To perform a partial match (a substring match) against numbers, you can use an array formula based on MATCH and TEXT.

Background

Excel supports the wildcard characters "*" and "?". 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).

If try to find a text value in a range of numbers, the match will fail.

Solution

One solution is to convert the numbers in the lookup range to text values, and then do a normal lookup with MATCH, VLOOKUP, etc. 

If this isn't practical, you can convert the numeric values to text inside a formula using the TEXT function or by concatenating and empty string to the range.

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 tranform the numbers in B5:B10 to text. Once the numbers are 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 tranform a number to text is to concatenate an empty string. This formula works the same as the formula above:

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