# 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

## Notes

Excel Function: TEXT(), VLOOKUP()

#### Question? Ask it in our Excel Forum

Our Excel Courses

#### Professional Input Forms in Excel • 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.

#### Send Emails from Excel (Multiple Methods) • 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...
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 ...
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...
Tutorial Details
Excel Function: TEXT(), VLOOKUP()