Partial Match Lookup with Numbers in Excel

Author:

Perform lookups on numbers with partial matches. For instance, find the first number that starts with 12 in a list. This requires an array formula in Excel.

The problem is that when you use the asterisk wildcard to find a cell that contains certain numbers, Excel converts the number to text and so you can't perform the lookup without changing the formula a bit.

Method 1

Method 2

Notes

Method 1

Use the TEXT() function.

Let's find the name of a person whose number begins with 12.

=VLOOKUP("12*",TEXT(A1:B3,"0"),2,FALSE)

Array Formula - this is an array formula and that means that you must enter it using Ctrl + Shift + Enter. This is a Vlookup function that is using a partial match wildcard, the asterisk.

The one change here is that the table_array argument is placed within the TEXT() function in order to convert the numbers to text so that the function will work correctly.

Here is the result: If you fail to enter it as an array formula or forget the TEXT function, you will get either a #N/A error or a #VALUE! error.

Method 2

Once again, find the name of a person whose number begins with 12

=VLOOKUP("12*",A1:B3 & " ",2,FALSE)

Array Formula - this is an array formula and that means that you must enter it using Ctrl + Shift + Enter. This is the same Vlookup function as in the last example, it performs a partial match lookup for a number that starts with "12".

The data in the table_array is still being converted to text here, but instead of using a function, like in the first example, we use a little trick to turn the numbers into text by adding a space to the end of them.

Here is the result: If you fail to enter it as an array formula, you will get either an error.

Wildcards in Excel

Vlookup Partial Match in Excel

Question? Ask it in our Excel Forum

Excel Function: TEXT(), VLOOKUP()

Similar Content on TeachExcel
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
Macro: This free Excel UDF allows you to get the numbers out of a cell that contain both text...
Store Large Numbers in Excel
Tutorial: I will show you how to display large, even huge, numbers in Excel.  In Excel, you can't ...
Generate a Non-Repeating List of Random Numbers in Excel - UDF
Macro: Generate a series of non-repeating random numbers in Excel with this UDF (user defined fun...
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
Macro: This free Excel UDF allows you to output the color of a cell in text format or as that col...
Convert Numbers Stored as Text to Numbers in Excel
Tutorial: I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situat...
Keep Leading Zeros in Numbers in Excel - 2 Ways
Tutorial: I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These t...
Tutorial Details
Excel Function: TEXT(), VLOOKUP()