Let’s take a look. Here we have the employee list we’ve looked at previously. This time, however, notice that the ID column has been moved into the data. It’s no longer the first column in the data, so we can’t use it to find values with VLOOKUP. This is actually a good example of a situation where you may need to use INDEX and MATCH instead of VLOOKUP if you need to match on the ID. We’ll cover that in a separate video. However, we can still use VLOOKUP in this case to match on the last name. First, I’ll name the data in the table “data,” and I’ll name the input cell in the search form “last.” Now I can enter the first VLOOKUP formula using “last” for lookup value, and “data” for table. To get the id, the column number is “3” and I want to use “0” for exact match. This works fine. If I type in “Irons” I get the ID for Julie Irons. But notice that I need to type in the full last name. If I type in “Iron,” I get NA, even though it’s a very close match. How can we adjust the VLOOKUP formula to allow a partial match? You might be tempted to change the last argument from “0” to “1” to allow “approximate” matching, but this won’t work. Instead, we want to keep zero as the last argument (to force an exact match), and adjust the lookup value in the function to include an asterisk as a wildcard. I can already do this manually. If I enter “iron*”, VLOOKUP finds the right ID. Because the asterisk matches one or more characters, “Iro*” also works. So does “Ir*”. If I want, I can also adjust the formula to automatically do a wildcard match. To do this, I just need to concatenate the asterisk to the lookup value inside the function. As always, literal text needs to be in double quotes. Now that the asterisk is in the formula, there’s no need to enter it manually. Now I can copy the formula down, and adjust the VLOOKUP functions to get values from the right columns. Notice that this is not a perfect solution. If I enter “ba*” we get information for Steven Batista, even though there are other last names that begin with “ba”. As always, in the case of duplicates, VLOOKUP will return the first match. However, it’s easy to adjust the lookup value to be more specific, so it’s still a handy tool when you want to allow partial matches.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
