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

Last updated on December 20, 2012 By

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.

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

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

VLOOKUP Tutorial Video Part 1

Watch this 13 minute video to understand the VLOOKUP function. Click on the video below to play it.

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.

Change the drop-down selection from EMP003 to EMP002 and watch the formula output

update

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.

  • Using COLUMNS function (Method 2) when the order of your lookup table headers does not match the order of your data table headers (e.g. “Full Name, SSN, Department, Start Date” vs. “Full Name, Department, SSN, Start Date”)
  • When using the MATCH function (Method 3) your lookup table headers do not match your data table headers (e.g. misspelling “Depatment” instead of “Department”)
  • Your absolute references might not be correct, and as you copy your dynamic VLOOKUP formula into other cells the COLUMNS() or MATCH() function could be looking up the wrong cells.

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.

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

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.

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.

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.

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

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.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

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.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.