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:
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 way to to transform a number to text is to concatenate an empty string (""). This formula works the same as the formula above:
Since MATCH alone isn't case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but the way we use it is a little unusual, because we need to compare one cell to a range of cells. Working from...
The MATCH function returns the position or "index" of the first match based on a lookup value in a range. MATCH supports wildcard matching with an asterisk "*" (one or more characters) or a question mark "?" (one character), but only when t he third...
The VLOOKUP function supports wildcards , which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table based on typing in only part of a lookup value. To use wildcards with...
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined...
The Excel TEXT function returns a number in a given number format, as text. You can use the TEXT function to embed formatted numbers inside text.
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.