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...
Tutorial: I'll show you 4 ways to convert numbers stored as text to numbers in Excel. This situat...
Tutorial: I will show you how to display large, even huge, numbers in Excel. In Excel, you can't ...
Macro: Generate a series of non-repeating random numbers in Excel with this UDF (user defined fun...
Tutorial: I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These t...
Macro: This free Excel macro formats a selected cell as an Accounting number. This means that the...