Discover a simple way to understand how VLOOKUP formulas work in Excel

[IMAGE] VLOOKUP Part 1If you are struggling to understand VLOOKUP formulas or want to explain VLOOKUPs to someone else who is struggling with VLOOKUPs, this article is for you!

Before we get started let me explain that VLOOKUP formulas are simple when you know how, but it can take a while to “get it”. A year or so after I started to learn Excel I stumbled across my first VLOOKUP formula. It was written by someone else, and I must have spent half an hour fiddling about with different inputs to figure out how it worked.

Back then the internet wasn’t quite so easy to search as it is today and I could not find any good online video tutorials, so I decided to look up Microsoft Excel’s Built-in Help on VLOOKUP. For a newcomer to Excel like I was back then, the instructions were hard to understand. It took me several months before I could write VLOOKUP formulas in my sleep.

Fast forward to the present day and I know that it’s easy to understand the VLOOKUP function when it’s explained onscreen using a good example. I really wish that someone explained the VLOOKUP function to me the way I’m going to explain it to you. It would have saved a lot of time and headache!

In our 13-minute video we explore a simple address book analogy to demystify VLOOKUPs and then check out how to use VLOOKUPs to perform exact matches. After that we examine the difference between exact matches and approximate matches and find out how to assign letter grades to students based on their percentage marks using the VLOOKUP function.

Image Credits:
CGI waves courtesy of “gerard79“.
Abstract circle shape courtesy of “iprole“.

Download the VLOOKUP Interactive Demo Workbook

[Image] Download Workbook In order to follow along with the video and article please download the Sample Workbook “VLOOKUP_Interactive_Demo.xlsx” by clicking here

Note: the sample workbook has been tested on Excel 2010 (32-bit, English language version) and you may get different results in other version of Excel. If you find that you can’t use this workbook on your version of Excel, please let us know using the comment section below.

VLOOKUP Tutorial Video Part 1

Watch our 13 minute video to help you understand the VLOOKUP function. Click on the video below to play it and listen to my audio commentary.

Excel VLOOKUP tutorial part 1
Runtime
13:03
View count
65,546

Video Transcript

Note: The video was transcribed by SpeechPad.com and then edited into the form you see below.

Let’s check out the VLOOKUP function. I’ll first take you through an analogy to show you conceptually what’s the idea of the VLOOKUP function in Excel. Then I’ll take you through a demo of the VLOOKUP with exact match, and then VLOOKUP with approximate match. After watching this video you’ll understand the difference between exact matches and approximate matches and when you can use them.

[Image] Address Book

Address Book Analogy: Four Steps to Writing a VLOOKUP formula in Excel

In the screenshot above, I have an address book with 5 different names in it. What we’re going to do is look up Gordon’s home address. We’re going to use a VLOOKUP function to do it. How will that work?

Basically we’ll go through 4 steps. These are the steps you’d take when you write a VLOOKUP formula. Let me scroll through them quickly, and then we will have a look at the actual format of VLOOKUP functions.

  • Step 1: tell Excel the name we want to look up; in this case, it would be Gordon.
  • Step 2: where is Excel going to find that name? It’s the whole table here, which I will just call Add_bk.
  • Step 3: What will Excel send back as the answer? Or how many columns along to the right should we look? This is actually column 1, 2, 3, and 4; so that would be four.
  • Step 4: Tell Excel if you want an exact match or the nearest match. Here we want to know exactly what Gordon’s address is, so we use an exact match. We set the fourth step to be false.

Let’s get started with writing our VLOOKUP formula

Let’s check out what the VLOOKUP formula with exact match looks like.

  • Step 1: I want to look up Gordon’s address, so I start writing ‘=VLOOKUP(Gordon‘ …
  • Step 2: Where is Excel going to find it? It will be the address book. I just shortened it to Add_bk. ‘=VLOOKUP(Gordon, Add_bk‘ …’
  • Step 3: What will Excel send back as the answer, how many columns along will I look? We need Column 4 for the address ‘=VLOOKUP(Gordon, Add_bk, 4‘ …
  • Step 4: I am going to use ‘False’, because we want an exact match. ‘=VLOOKUP(Gordon, Add_bk, 4, FALSE)’

What you’re doing is saying; lookup the name Gordon, which is right here in the address book table, with the names having to be in the leftmost column. Checkout the fourth column and then return an exact match. We get the answer, 974 Gordon’s Hill.

[Image] Gordon's Hill

Here is the result of our VLOOKUP formula to find Gordon's address

Using the VLOOKUP Function to look up other columns in the Address Book

I like using the address book analogy to explain the VLOOKUP formula because it helps you remember that you’ve got to have something to look up in a table of data.

In this case we looked up Gordon’s address but if we switched the 4 for a 2, we would get Gordon’s home phone number. If we switched it for a 3, we’d get Gordon’s work number. If we switched it for 5, we’d get Gordon’s email address.

Basically that is how VLOOKUPs work. We are going to head over and check out an interactive demo.

[Image] VLOOKUP with exact match

VLOOKUP Function Part 1: Exact Match

Let’s start with the VLOOKUP formulas with exact match. The spreadsheet’s setup so that you change the user inputs and cells C9, and E9, and then you watch the output in G9. As well as watching the output which in this case, is Faith K. Macias, you will be able to see whereabouts in the table it is pulling information from.

That output cell has the VLOOKUP formula which says ‘=VLOOKUP(‘. The first parameter is ‘lookup_value’. The second parameter is ‘table_array’. The third parameter is ‘col_index_ num’. The fourth parameter is ‘range_lookup’. All that is taking it’s input from these 4 cells.

What I will show you now it what happens when I change input value.

[Image] Dropdown to change VLOOKUP input value

Let’s say, instead of looking up EMP001, we look up EMP002. Select from the dropdown ‘EMP002′. You’ll see employee two full name, which is coming from col_index_2 Lucian Q. Franklin. You can do the same if you scroll through to EMP003, Blaze V. Bridges, and so on, down all these employees.

You can play around with that input value or you can play around with the column index number.

Instead of saying you want to lookup column 2, we might want to say let’s lookup column 3. Here you see the output cell changes to show the Social Security Number, which is in this case, 845-04-3962. By the way, none of these are real people, they’re all made up.

The column number you can play with by changing the value in this. Here I’ve changed it to 4, which is the department. I can see EMP001 is in Department Marketing. Column 5 is their start date and column 6 is the earnings they have.

Format of output does not change to match the values found by VLOOKUP

Notice that the format for the cell doesn’t update, so the format I’ve got in the output cell is just the standard output, a general format. If you want it to return dates, you would have to format that into a date format, because at the moment, all you see is Excel’s numerical representation of the date.

You can use “FALSE” or “0″ to signify an exact match

I’ve also included this False and 0, because you can either write the formula as ‘=VLOOKUP(EMP001, tbl_employees, 5, FALSE) or you could write 0 instead of FALSE. The key here is to remember if you want an exact match the last parameter, ‘range_lookup’, it has to be either 0 of FALSE.

If you scroll over to the right, in this worksheet, you’ll see a little explanation of the function syntax. A quick explanation of how it works, when you can use it, and what to look out for.

Time for a Break!

Before we move on to the next section which looks at VLOOKUPs with approximate match instead of VLOOKUPs with exact match, I’d like to show you a nice soothing background to help you relax.

[Image] Pebble Tower

Time to take a mental break before we move onto VLOOKUP with Approximate Match

This is a cute little pebble tower, and I’m hoping that you’ll be able to take a few breaths in and out. We will on to the mind-bending VLOOKUP with approximate match, and you can be nicely relaxed. OK. Breath in and out, relax, and let’s move on.

[Image] Table showing 10 employees

Let's do a quick recap of how VLOOKUP with Exact Match works

VLOOKUP Function Part 2: Exact Match recap

We’ll start with a quick recap of the VLOOKUP with exact match and what it does. If you look at this table of employees and employee data, you’ll see 10 employees on the left-hand column.

Basically what we did was we decided to select EMP003. The lookup formula would go down the left-hand side and go, ‘Where’s 1, 2, 3; there’s 3.” Then we decide which column do we want to look at? Let’s say to we want to look at the full name which is in column 2.

It would then go, “EMP003, Column 2, return Blaze V. Bridges.” We could do the same for column 3, 4, 5, and 6. The critical thing to note is that Excel only returns a value if it finds an exact match to EMP003.

If for example you had EMP03, with one less 0, it would not find a match. If you had EM003 without the P, then it would also not find the match and it would give you an error message, #N/A. That is the exact match. What’s the difference with that and the approximate match? Let me show you a new table.

[Image] Student grades

Here is a new table with Student Grades that we'll use to see how VLOOKUP with Approximate Match works

VLOOKUP Function Part 3: Approximate Match

Here is a table of students and the marks they’ve got. What we’re trying to do is work out, with their marks, what letter grade they should get. Just to be clear, the marks are expressed as percentages: 20%, 32%, 48%, 56%, and 60% and so on.

What we’re going to do is look up the table on the right, which is the Marks table.

  • If they get anything between 0% and 30%, they receive a grade E.
  • Anything between a 30% and 60%, they’d receive a grade D.
  • Anything between a 60% and 80%, they’d get a grade C.
  • Anything between 80% and 90%, they’d get a grade B.
  • Anything from 90% or above, they’d an A-grade.

What we’re doing here is instead of looking up exact values, we’re looking up the nearest match.

In this case we’ll start with the first student. STU001 had 20%, from the table over here, we will see 20% will be a grade E. STU002 got 32%. If you’ll look to the right, 32% would fall into a grade D. STU003, with 48%, would also get a grade D. STU004, with a 56%, will also be a grade D. STU005, with 60%, should be getting a grade C.

What we did here is looked up the table, we looked for the nearest match which was less than or equal to the number we had, and then we returned the grade. You can do all that with a VLOOKUP formula. I’ll show you how.

You can use “TRUE” or “1″ to signify an approximate match

With the VLOOKUP formula, we have exact same syntax as for exact matches. You got the same 4 parameters, which are lookup value, table array, column index, and range lookup. In the case of an approximate match, the fourth parameter “range lookup”, is set to TRUE or 1.

When you set “range lookup” to TRUE or 1, it will do an approximate match for you; this is basically how it works. It will tell Excel to lookup the table, in this case the table_marks, which is this one here. Look down the left-hand column until it finds a value greater than lookup value, then it goes back up 1.

[Image] Marks Table

Looking up 60% in the grades table brings back Grade C (using VLOOKUP and Approximate Match)

In this case, we had a mark of 60%. We go to the Marks table, we find something that’s greater than 60%: Not 0, 30, or 60. Eighty; that’s the first value that’s greater than 60%, and we go back one row. It would go, “We get 60,” and then return the grade here, because I’m telling it to return column index number 2, so that would look up the 60% and give you a grade C.

Warning: Sort the left hand column ascending for approximate matches

The important and vital thing you’ve got to lookout for is if you want to do approximate matches with the VLOOKUP formula, you have to sort the left-hand column ascending, because otherwise, you won’t be able to get the right results.

If you use the VLOOKUP approximate match, you’ve got to sort your table with the left-hand column ascending from the lowest value to the highest value.

Recap of what we covered

VLOOKUP Function:
1. Address Book Analogy
2. VLOOKUP Exact
3. VLOOKUP Approximate

To briefly recap this session, we checked out the address book analogy to explain how VLOOKUP works. We then went on to use VLOOKUP for exact matches. Finally, we bent our head around VLOOKUP for approximate matches.

Go forth and do lots of LOOKUPS!

In part 2 “VLOOKUP examples” we explore how to write VLOOKUP formulas with more flexibility, including how to create dynamic VLOOKUP formulas using the MATCH function. In the meantime make sure you download the sample workbook and get familiar with writing simple VLOOKUP formulas. The more you write the quicker you will get, and the more you’ll understand other people’s VLOOKUP formulas.


About-Launch-ExcelWelcome to Launch Excel
If this is your first time to LaunchExcel.com - Welcome to Launch Excel. My name is Victor and I'm the Chief Teaching Officer of Launch Excel, a website I started to help you learn how to use Microsoft Excel more effectively. Check out my welcome page created specially for you: Click here to view Welcome Page


8 Responses to Discover a simple way to understand how VLOOKUP formulas work in Excel
  1. rich
    December 20, 2012 | 6:22 pm

    on this video you use some tool to do the Highlighting (the 1 / 2 / 3 / 4 and red/blue text)
    What tool are you using – I want my sig other to get it for her chemistry class where she is starting to create vieos of lessons and science fair projects

    Thanks

  2. Lucy Ann
    January 2, 2013 | 6:52 am

    I’m trying to devise a spreadsheet in excel that has an annual amount of $14,999 multiplied by 11/12th = $1,249.92 to equal 1 week/month. EX:
    $14999 92% $13749.08. So this formula has to be repeated for 52 weeks. How do you write a formula in excel that will return to a prior cell and retrieve 13749.08 reduce it by 1,249.92 & calculate the percentage on a go-forward basis?
    EX: the next should read:
    13749.08 83% $12499.08. This formula should be able to drag across to represent an accurate 52 weeks. Thank you, Lucy Ann

  3. [...] works, or know someone else who is struggling with VLOOKUPs, check out the first article “Discover a simple way to understand how VLOOKUP formulas work in Excel“, and come back to this [...]

  4. Tobi Hossner
    March 20, 2013 | 12:04 am

    This is a great explanation of VLOOKUP function Victor. I was thinking to write an article on muliple conditional vlookup formula. Do you have one already I could refer instead?

    • Victor
      March 21, 2013 | 9:06 pm

      Hi Tobi – thanks for your comment. I actually don’t have an article on multiple conditional vlookup formulas, and it would be great to see your article if and when you write one.

      Your site excelformulaslist.com looks interesting, are you planning to focus on Excel formulas and only Excel formulas?

      • Tobi Hossner
        March 21, 2013 | 9:52 pm

        Idea was to create a bank of explanations for each function and after go with articles on anything around Excel in a blog format.

        But yes, mainly on formulas. Full time job is taking too much to spare writing time, hence, going slow, but will get there..

  5. Thandava Moorthy
    June 22, 2013 | 9:57 pm

    Very nice, Thanks for ur help