## Subscribe for Weekly Tutorials

### BONUS: subscribe now to download our Top Tutorials Ebook!

# 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

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:

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.

## Question? Ask it in our Excel Forum

Macro: This free Excel UDF allows you to get the numbers out of a cell that contain both text...

Macro: This free Excel macro formats a selected cell as an Accounting number. This means that the...

Macro: Generate a series of non-repeating random numbers in Excel with this UDF (user defined fun...

Macro: This free Excel UDF allows you to output the color of a cell in text format or as that col...

Tutorial: I'll show you 4 ways to convert numbers stored as text to numbers in Excel. This situat...

Tutorial: I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These t...