Partial Match Lookup with Numbers in Excel

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.

Sections:

Method 1

Method 2

Additional Resources

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

Add a space.

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.

Additional Resources

To learn more about wildcards and partial matches for Vlookups, visit our tutorials:

Wildcards in Excel

Vlookup Partial Match in Excel

Notes

You can do partial lookup matches with text without all of the issues show here; this tutorial is specific to partial lookups on numbers. The problem is that when you do a partial lookup, Excel will always be searching for text, even if you enter a number; and Excel won't be able to find text in a list of numbers.

Make sure to download the sample file for this tutorial to work with these examples in Excel.