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.

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

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...
Format Cells as a Scientific Number in Excel Number Formatting
Macro: This free Excel macro formats selected cells in the Scientific number format in Excel. Thi...
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...