Partial Match Lookup with Numbers in Excel

Add to Favorites

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

Additional Resources


Method 1

Use the TEXT() function.

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


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


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.

Question? Ask it in our Excel Forum

Excel Function: TEXT(), VLOOKUP()
Downloadable Files: Excel File

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...
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...
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...
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...
Format Cells as an Accounting Number in Excel Number Formatting
Macro: This free Excel macro formats a selected cell as an Accounting number. This means that the...