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.

Video

Narrative

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.

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:

“*”&B7

And, we could search for a row that contains the value in B7 like this:

“*”&B7&”*”

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!

 

 

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

9 comments:

  1. Traci Hancock
    Reply

    Thank you for this hack! I’ve wanted to do this before and ended up doing something much more cumbersome. Also, thank you for providing the written narrative. The company I work for doesn’t allow us to access YouTube, so the narratives are all I can use and your hints have been very helpful! Keep up the great work.

    1. Jeff Lenning Post author
      Reply

      Thanks Traci, and I love this hack too πŸ™‚

  2. Cynde LaFaire
    Reply

    Oh my gosh, this is so helpful! Thank you! I have been going through a lot longer process just to get “subtotal” out of my vlookup. This way is so much easier! You just made my day.

    1. Jeff Lenning Post author
      Reply

      Yay … happy to help πŸ™‚

  3. Mickael Taieb
    Reply

    Great hack, thank you very much!

  4. Tax King Inc
    Reply

    I need help with MS Excel. I have a long list of Bank Account transactions. I would like to code every single item. I don’t want to type anything, I want to upload a chart of account in a spreadsheet and want to fill every single cell selecting item from chart of Account

    Anybody can help me with that ?

  5. Duncan
    Reply

    Hi Jeff, very useful. Is there a way to do this in reverse – where your lookup table contains a substring of your source table?

  6. Benjamin Lund Fabricius
    Reply

    I am trying to do the reverse as well. My best shot is this vba script.
    you can use it with INDEX(ARRAY; MyMatch(lookup_value; lookup_array);col_num).

    It actually works, but i would like to find a way with out the VBA script.

    “Function MyMatch(lookup_value, lookup_array)
    res = “”#N/A””
    For i = 1 To Len(lookup_value)
    For j = 1 To lookup_array.Rows.Count
    substr = Left(lookup_value, i)
    If substr = lookup_array(j) Then
    res = j
    End If
    Next j
    Next i
    MyMatch = res
    End Function”

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

If you agree to these terms, please click here.