Vlookup in Excel

Vlookup (pronounced “vee-look-up“) is one of the most popular look up functions in Excel.  When you need to extract data from a table based on a particular value, you can use Vlookup to do that.  In simple terms, it looks at a table in the first column for a specific value and returns a cell in the same row where you choose the column to return from.  You get to choose whether you want to find an approximate match or an exact match (more on which one to choose later).  You can use this for both text searches as well as numeric searches, which proves to be a powerful function and can easily help you out in your everyday tasks at work.

What we’ll be talking about:

  • Vlookup function basics
  • Vlookup with text searches
  • Vlookup with numeric searches
  • When to choose approximate match versus exact match

Let’s get started.

Vlookup Function Basics

The Vlookup function has the form of:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup = TRUE])

Here is a simple explanation of each parameter (for those of you who don’t know, a parameter is, check out this post):

Parameter Notes
lookup_value The number or text sought for.
table_array cell reference of the table range.  Note: Do NOT include headers in your cell reference if you are searching for text and an approximate match.  Trust me, you won't get the desired results in some cases (more on that later).
col_index_num The number of the column you want to return.  This number is based on the table you pass, not by the column character's number equivalent.  For example, if your range is B2:D5, column 1 is B, column 2 is C, and column 3 is D.
range_lookup Can either be TRUE or FALSE.  It s an optional parameter where if left out, the value is set to TRUE.  If TRUE, the function will look for an approximate match.  This means that for a numerical search, it will return the closest number smaller than the value you're looking for, if it does not find an exact match.  If FALSE, the function will look only for an exact match.  And if no exact match is found, an #NA!error will be returned.  I should note that if you choose TRUE for range_lookup, the first column in the table must be sorted from smallest to largest (or for text, alphabetical).

Vlookup when searching for text

Here I create a simple table to illustrate using Vlookup when searching through text.  Let’s say that I want to find the age of someone based on the name we’re searching for.

Vlookup Table

(If you’re curious as to how I have those little dropdown buttons next to each header name, select your table – including the headers – and click Sort & Filter -> Filter)

Say we want to find Ray’s age using the Vlookup formula.  Here’s how we would do it:

=VLOOKUP("Ray",$A$2:$B$6,2,FALSE)
Result: 31

Here is the formula breakdown (each parameter is separated by a comma):

Parameter Notes
"Ray" The text we're searching for in the first column of the table array we will pass.  (Hint: this can be a cell reference instead of just plain text)
$A$2:$B$6 The table array.  We have to include column B because that is what we want to return based on the text we find in A.
2 This is the column number.
FALSE This tells the function that we want an exact match.  A nice thing about using FALSE here is that we don't have to worry about the table being sorted.  I'll get into what happens when we use TRUE in a little bit.

When the formula is evaluated by Excel, it looks in each row of column A for an exact match on “Ray” which we passed as the parameter to search for.  The formula then finds “Ray” in the second row of the table we passed (remember, the first row/column starts and the top-left-most cell in the table passed to the function).  When it finds “Ray” it moves to column 2 in the table of the same row and returns 31, which is correct.

Vlookup when searching for numbers

It’s more likely that you’ll search for an approximate match when looking for a number when using Vlookup (or at least that’s what I think, anyway).  Check out this basic table showing temperature and corresponding “Real Feel.”

Vlookup Table Temperatures

Let’s look at some of the return values by playing around with the Vlookup formula.  I’ll do a Vlookup on a temperature of 0.03 using this formula:

=VLOOKUP(0.03,$A$2:$B$8,2,TRUE)
Result: "Ridiculously Cold"

Let’s look at the formula breakdown and then we’ll step through how it came to that result:

Parameter Notes
0.03 The number we're trying to find a close match for.
$A$2:$B$8 The table array (again, including column B so we can return the "Real Feel").
2 The column number we want to return.  In this case, the "Real Feel" value.
TRUE This time we tell the function that we want an approximate match.  So when we looked for 0.03, we found a match for 0.01 in the table.  Again, remember that when we set this value to TRUE we must have the table sorted and we cannot have the header as part of the table range (I'll explain why in a little bit).

The way Excel steps through this formula is similar to the last one we looked at:

  1. It starts by looking at the table’s first column for a value of 0.03 or something close (approximate).
  2. Starts with 0.01.  Since 0.01 is less than 0.03, it moves to the next one.
  3. Then it looks at 32.  Since 32 is greater than 0.03, it reverts back to 0.01 as the closest match.
  4. Now it goes to column 2 and return the corresponding value next to 0.01’s value, which is “Ridiculously Cold.”

If we were to change TRUE to FALSE, we would receive the dreaded #NA! error since there is no 0.03 value in the table.  The range_lookup value can be more important than people realize when using Vlookup.

So what if we tried searching for 31 instead of 0.03?  Should we have “Freezing Cold” be returned?  Let’s take a look.

=VLOOKUP(31,$A$2:$B$8,2,TRUE)
Result: "Ridiculously Cold"

What?!  It returned “Ridiculously Cold”?  But 31 is WAY closer to 32 than it is to 0.01.  What gives?

Remember what I said before about the range_lookup parameter for numerical searches?

For a numerical search, it will return the closest number smaller than the value you’re looking for, if it does not find an exact match.

Let’s step through how Excel evaluated this formula:

  1. Is 0.01 less than or equal to 31?  Yes, it is less.  So keep moving down.
  2. Is 32 less than or equal to 31?  No, so we can stop looking.
  3. 0.01 is considered the closest approximate match in this scenario, as odd as it may seem.

It’s clear that searching for an approximate value will sometimes seem less logical than we think it should behave.  Let’s look at the quirks of Vlookup and see if we can make some sense out of it.

Approximate match or exact?  Which one should I choose?

The range_lookup parameter is what controls whether we tell the Vlookup function should look for an exact match or an approximate match.  This setting can be a bit tricky so it’s good to remember how the function will be behave based on this value and which one you should choose when using the Vlookup function.

Exact Match

This is just my opinion, but I feel that searching for an exact match makes this function much easier to use.  This is especially true when searching for a text value (which I feel is what most people actually use when people use text for the range_lookup parameter).  Chances are you would only want to find an exact match when searching for text.

A nice feature of searching for an exact match is that you don’t have to worry about sorting the table by the column you’re searching for.  This way, when your table grows, the formula still works correctly.

Also, with exact searches, you can safely include the header row when you pass the table parameter.

Approximate Match

Approximate matches are probably best-suited for numerical searches.  Searching for exact matches might be more difficult when your numeric values contain fractions of numbers.  I would imagine that in most cases, looking for an exact numeric match would be very difficult when dealing with fractional numbers.  I feel that approximate match is more appropriate in this kind of situation.

When searching for text, however, it’s difficult to find a good reason to look for an approximate match.  And what would be considered an approximate match, anyway?  If we search for “Baub,” would “Bob” be an approximate value?  In this case, no.  That’s because alphabetically, “Baub” comes before “Bob.”

Yet, there are times where the approximate match doesn’t seem to make the most sense like we saw earlier.  I guess it’s in the name “approximate.”  When you hear that, you think of what’s closest to what we’re looking for.  To help remember how approximate matching works, think of it as “the closest match less than or equal to the value sought for.”

What’s with excluding the header row?

OK, I know I mentioned this a few times in this post.  Here’s the thing, when you search for text using approximate match and you include the header row as part of your table, you will not always get the desired results.  Let me show you an example.  Let’s look at our first table:

This list is sorted by name.  Check out this formula:

=VLOOKUP("Bob",$A$1:$B$6,2,TRUE)
Result: #NA!

We have the range_lookup value set to TRUE which means to look for an approximate match.  Yet we get an error in return.  This is really strange, because if we change the formula to this:

=VLOOKUP("Bob",$A:$B,2,TRUE)
Result: 27

It doesn’t complain.  In my opinion, try to either use FALSE for range_lookup or just don’t include your headers if you have to search for an approximate match.

 

I hope you enjoyed this post and if you did, please subscribe!  You will get notified of newer posts the moment they are published.  Also, if you have any questions or comments, or if you want to see me post about something, please leave a comment below.  It really helps me to make this blog better and I would love to hear from you.

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

  • Oz

    Hey! Love the blog, and I just subscribed.

    May I add something about the approximate match? It’s something that I researched because it perplexed me, also.

    One requirement of the approximate match is that the range_lookup has to be sorted in order. Thus, it can work for numbers and text.

    Because everything has to be in order, the approximate match is much quicker because it searches by splitting the list in halves.

    Al
    Brenda
    Darla
    Mike
    Rhonda
    Tony
    Walt

    If we’re looking for Tony, the approximate match will start in the middle of the list with Mike. Alphabetically, Tony is after Mike. The middle of the lower half of the list is Tony. And we’re done. That’s quicker than the exact match which starts at the top of the list and goes one-by-one (6 steps instead of 2 steps).

    This isn’t a big deal with 7 entries. But if you have many thousands, and a lot of VLOOKUPS, that’s when the speed of Approximate might be noticeable.

    ===
    Keep going! I like what you’re doing here with your content and humor. It’s a breath of fresh air.

    • Hi Oz!

      I had no clue that VLOOKUP used binary search trees for Approximate matching. That makes a lot more sense now when the Excel documentation states that if the data is not sorted, you’ll receive quirky results. I’m going to play around with this some more and maybe I can make a post about it (I’ll be sure to credit you for informing me!)

      Thanks so much for the compliment! I’m very happy you like my content. I have some new ideas for the humor aspect that I hope you like. We’ll see how it goes 🙂

  • Carol

    Hello, I am a follower of your blog, it has really helped me learn some cool tricks! which I use at work all the time. Quick question: is there a shortcut in Excel that allows me to enter today’s date in the current cell?

    I appreciate your help!

    Thanks,

    Carol

    • Hi Carol!

      Thanks for following my blog 🙂

      As for your question, yes, you can add the current date in a cell by pressing Ctrl + ; (Control and Semi-colon).

      Take care,
      Joseph

      • Carol

        Awesome! I will start using this now 🙂 Thanks a lot!

  • Walter

    What if you have more than one person with the same name? When I run a Vlookup it only retrieves the first name in the series and not the rest of the names. Any suggestions?

  • Souvik

    Hey Joseph,

    You have a wonderful explanation of VLOOKUP. I wanted to know that if there are cell/row gaps in the table_array, will the formula work correctly? Because I cannot work the formula when there are cell gaps in the data (I get a lot of such data at work, actually).
    And I too, have the same problem as Walter mentioned earlier, but I would like a simplified solution, really.

    Thanks in advance, mate.

  • Matt Dunn

    An alternative way of putting it from http://www.excelvlookuphelp.com
    A vlookup looks for a value in the leftmost column of a table and then returns a value from that same row (how far along that row is determined by you).

    The vlookup formula consists of 4 parts:
    1.SearchFor this is the value that is being searched for. In our dictionary example it would be the word “elucidate”.
    2.WhereToSearch this is the range in which to search and in which the answer lives. In our example, it would be columns A to C.
    3.WhichColumn this is the column in the WhereToSearch range which has the answer in it. In our dictionary example, it would be the third column.
    4.NearOrExact you decide whether the vlookup should search for a close value or an exact value (in our dictionary example we would set this to false as we do want it to search for the exact word “elucidate”).

    Structure of the vlookup formula:

    =vlookup ( SearchFor , WhereToSearch , WhichColumn , NearOrExact )

  • Emily

    this is honestly so great. I love the layout ????