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.

Sections:

Method 1

Method 2

Additional Resources

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.

d85c2ab6442598c61319757222151518.png

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:

a1a35ba7d557c146a1ce762e919b4057.png

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.

265836a69c5131ae79111538f7a139fa.png

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:

19137f83e8ba9a54bf6707e609e1f102.png

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

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.


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...
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 tha...
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
Macro: This free Excel UDF allows you to output the color of a cell in text format or as that col...
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...
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...