If 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.
Watch this 13 minute video to understand the VLOOKUP function. Click on the video below to play it.
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.
Change the drop-down selection from EMP003 to EMP002 and watch the formula output
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.
Let’s check out what the VLOOKUP formula with exact match looks like.
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.
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.
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.
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.
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.
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.
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.
Let’s do a quick recap of how VLOOKUP with Exact Match works
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.
Here is a new table with Student Grades that we’ll use to see how VLOOKUP with
Approximate Match works
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.
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.
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.
Looking up 60% in the grades table brings back Grade C (using VLOOKUP and
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.
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.
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.
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.
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.