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.
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.
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.
To learn more about wildcards and partial matches for Vlookups, visit our tutorials:
Vlookup Partial Match in Excel
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.