# 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...
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...
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...
Store Large Numbers in Excel
Tutorial: I will show you how to display large, even huge, numbers in Excel.  In Excel, you can'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...
Tutorial Details
Excel Function: TEXT(), VLOOKUP()