Discover a simple way to understand how VLOOKUP formulas work in Excel
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.
Download the VLOOKUP Interactive Demo 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.[tubepress video=”4G94ypt5MUg”]
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.
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.
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.
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.
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.
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.
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.
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.
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
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.