VLOOKUP Hack #9: Partial Match
Let’s say we want VLOOKUP to match the lookup value “North Region” with “North Region Subtotal” stored in the lookup range. We started this series by looking at the 4th argument. We know it can be TRUE or FALSE. FALSE means exact match and TRUE means approximate match. So, what exactly is an approximate match? Well, as you may have guessed, we’ll dig into that and hack the 1st argument to accomplish our true objective: a partial match.
I’ve created a video demonstration and a written narrative for reference.
Note: depending on your version of Excel, you may have XLOOKUP as an option … more info here: http://www.excel-university.com/xlookup/
So, let’s just confirm what we are talking about. We’ve stored the region in cell B7 as shown below.
Now, we’d like to write a formula in C7 that retrieves the corresponding amount from Table1, shown below.
So, we start off with something like this:
=VLOOKUP(B7, Table1, 2, FALSE)
And, we hit enter, and get #N/A as shown below.
After reflecting on this, it makes sense. It makes sense because we used FALSE (or 0) for the 4th argument. That tells VLOOKUP to perform an exact match. Since “North Region” in B7 doesn’t match exactly to “North Region Subtotal” found in the table, we get an error. We discussed this long ago in Hack 3.
But, we know that the 4th argument has two possible choices. TRUE is described as Approximate Match, as shown below.
So, we give that a try. Drats! Same error, as shown below.
Ah, but then we remember something about the sort order mattering. We discussed this a while back in Hack 1.
So, we sort the table in ascending order by the first column. As shown below.
When we inspect our formula, it actually returns the value for the East Region, as shown below.
Yikes! What is going on here? Well, as we discussed in Hack 2, when we set the 4th argument to TRUE, we are really asking Excel to perform a range lookup. When VLOOKUP performs a range lookup, it scans down the first column. It returns the row where the table value is greater than or equal to the row and less than the value in the next row. While this makes perfect sense to us when the range is made up of numbers, it is a little harder to visualize when our lookup column has text values. But, effectively, the text string “North Region” is actually less than “North Region Subtotal” so the value from the East Region Subtotal row is returned.
So, it seems like we don’t want an Exact match. And, we don’t want an Approximate match. But, these are the only two choices for the 4th argument. TRUE or FALSE. There isn’t a third option. So, what are we supposed to do? Do it manually? No…of course not! That brings us to the hack.
What we are really after is a partial match. A partial match is where only part of the text needs to match. In this case, we want the lookup value “North Region” to match to the row that begins with “North Region” even if it includes something after, like “Subtotal” or a bunch of extra spaces. For now, let’s think about it like this. We want to find a row in the lookup range that “Begins With” our lookup value. How do we do that? Here’s the hack.
Hack: use a wildcard in the 1st argument
A wildcard is a character that can stand in to represent other characters. For example, the asterisk * can stand in to represent any number of characters. So, we need to join our lookup value to the asterisk. And, to do that, we’ll need an assist from the concatenation operator we discussed in Hack 7. The asterisk needs to be enclosed in quotes, so, our updated formula looks like this:
=VLOOKUP(B7&"*", Table1, 2, FALSE)
Now when we hit Enter … yes, it works!
Using the wildcard, we are asking VLOOKUP to go find a value in the lookup column that begins with the value in B7.
We could also search for a row that ends with the value in B7 like this:
And, we could search for a row that contains the value in B7 like this:
The sample file below contains the formulas in case you’d like to check them out.
If you have any other related VLOOKUP hacks, please share by posting a comment below!
- Sample file: VLOOKUP Hack 9.xlsx
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.