The Last Guide to VLOOKUP in Excel You’ll Ever Need

Written by Kasper Langmann, Microsoft Office Specialist. Updated September 7, 2020.

VLOOKUP is one of the most well-known Excel functions – and not without reason!

VLOOKUP’s ability to “looking up” data is unparalleled in Excel…

… But it’s quite hard to use VLOOKUP!

So, I’ve made it super simple for you 🙂

Simply follow my 6 steps to using VLOOKUP – and you’ll get it right EVERY time.

Scroll down and let’s get started!

What is a VLOOKUP?

The VLOOKUP function looks for something in a range of cells.

Then the VLOOKUP returns that something that’s in the same row as the value you’re looking for.

The only caveat is, that the datasheet has to be listed vertically.

Now you know what a VLOOKUP is…

… but what’s more interesting is what it can do!

Keep reading…

Get your FREE exercise file

To help you better get the hang of the VLOOKUP function, I’ve created a free exercise file for you.

The rest of this guide is based on the data in the file, so make sure to get it!

Let’s put VLOOKUP into action!

Let’s see the VLOOKUP function in action!

Say you’re looking for Nate Harris’ salary (based on the data in the exercise file).

It’s a waste of time to look for his name in column A and then type his salary in the cell you need it to be in.

VLOOKUP can do the trick in less than 1 minute!

Let’s build a tool powered by the VLOOKUP function where you enter an employee’s name and then automatically see his or her salary in seconds.

Building a machine like this is split up into 6 steps, let’s go through each one step-by-step!

(Every time you need to use a VLOOKUP function, follow these 6 steps)

How to use the VLOOKUP

You create a VLOOKUP function in 6 steps:

Step 1: Placing your VLOOKUP.

Step 2: What are you looking for?

Step 3: Where are you looking?

Step 4: What do you want to return?

Step 5: Do you want to be precise or approximate?

Step 6: Press ‘Enter‘!

Don’t worry, this process isn’t nearly as sophisticated as it sounds.

Now, let’s take a closer look at our sample file.

Example of different steps in using a VLOOKUP function.

Let’s start with step 1: placing your VLOOKUP…

Step 1: Placing the VLOOKUP formula

Start by selecting the cells where you want the result of your calculation (the VLOOKUP function) to go.

We’re going to be looking for something, so include a spare cell to use for “searching”.

In this case 4 cells in total.

Two for the name and two for the salary.

  1. Select cells E2:F3. Go to the “Font” group of the “Home” tab on the ribbon and click the little arrow next to the “Borders” button.
  2. Right-click and select “Format Cells”. Then go to the “Border” tab.
  3. Select a thick border from the “Style” options and click on the “Outline” preset. Then click the horizontal line in the mid of the example.
  4. Now select a thinner border style and click the vertical line in the mid of the example.
  5. Type in “Name” in cell E2 and “Salary” in E3 and make the text bold.

Now we have a pretty little area to fill in what we’re looking for (cell F2) and our formula with VLOOKUP below (cell F3).

That was pretty simple, right?

Move on to step 2…

Step 2: What are you looking for?

Now it’s time to get into the syntax of VLOOKUP.

Here’s what “syntax” means in the case of an Excel function:

“Syntax is a combination of the things you need to put into a function to make it work.”

A VLOOKUP function needs these 4 inputs to work:

  • Lookup_value = What you are looking for
  • Table_array = Where you are looking
  • Col_index_num = What we want to know
  • [range_lookup] = Whether we want to be precise or approximate in our search

All these inputs to the VLOOKUP formula must be separated with a comma.

When you write a formula, you’ll see all of these phrases inside a tooltip box below the cell you’re typing in.

Every time you put a comma, the tooltip box tells you how far you are in the formula, by bolding the current part of the syntax.

Here’s how to make sense of VLOOKUP’s syntax!

Imagine the VLOOKUP function doing the same as you when you want to look something up.

phonebook-exampleSay we have a  book with people’s names, addresses, phone numbers, and salaries.

  • You’re looking for Nate Harris’ salary.
  • You acknowledge that you’re looking for the name ‘Nate Harris’ in the book. This is your identifier. The piece of information you’re searching for with your eyes. In Excel terms, this is your lookup value.
  • You browse through the pages and find Nate Harris.
    • You place the tip of your finger right below his name and move your hand to the right past information like his address, phone number, and other boring stuff. And there you have it! His salary! 3 inches to the right of his name. Then you write it down or do whatever you want to do with it.

That’s exactly how a VLOOKUP function works!

So our lookup value is typed into cell F2 and then used in our function.

I guess you’re getting the point by now? So let’s start building the formula!

Type this into Excel in cell F3 (also shown in the screenshot right below):

=VLOOKUP(

This is a breakdown of the VLOOKUP function. The textboxes explains the syntax of the function.

What comes next is the name of the employee we want to find more information about.

As a lookup value, you can either type in the name (“Nate Harris”) or put in a reference to the cell where you’ll type in the name.

The last option is usually the best and our sheet is set up to have the name entered in cell F2.

So we’re going to select cell F2 (or type F2 in the formula bar) and type a comma to move on in the formula.

One of the early steps in creating a VLOOKUP formula. Here the lookup value is just entered.

In this case, the VLOOKUP formula will not be copied, but the sheet structure might be changed at some point.

To prevent the formula from being messed up, we lock the reference to cell F2.

Do this by putting the marker inside (or around) the F2 and pres the shortcut F4 (on MAC: Command + T).

Like done below:

This shows how you can lock the reference to the lookup value in a VLOOKUP function.

Your formula now looks like this:

VLOOKUP formula when the lookup value reference is locked.

Step 3: Where are you looking?

This step is where we are looking for “Nate”.

When you are looking for Nate Harris manually, where do you look?

Well, Excel “looks” almost the same place!

So we’re looking in column A of the data – the one called ‘Full Name’ – Excel is also looking in column A.

However, the VLOOKUP function needs to know the entire dataset in order to return the information you want later on in step 4.

So select (or type) range A2 through C55 and press F4 (MAC: Cmd + T).

Now the reference to the range is locked and you can move on to the next step by typing a comma.

Your formula should look like this:

2nd argument of a VLOOKUP function in action. This is the table_range.

Step 4: What do you want to return?

When you use a VLOOKUP you want an answer to something.

What you want to return is what Excel calls the col_index_num (Column Index Number).

In short: Which column in the data you want to return data from.

When you’re looking for Nate Harris and find him in row 17 of the sample data, the column index number determines which column in the data the result will come from.

It’s as simple as this.

The column index numbers in a dataset for a VLOOKUP formula.

Column A has Column Index Number 1, column B has 2 and column C has 3.

That seems pretty straightforward, but you need to be aware of the following:

The column index number is relative to the data you selected in step 3.

You selected the cells A2 through C55. That means that the data stretches 3 columns wide.

In this case, we want to return data from Column C which is column index number 3. But if the data was located differently in our Excel sheet, then column C would not have column index number 3.

Look at the picture below.

This is how the Column Index Numbers in the VLOOKUP function would be if the data was located in Column E through G.

In this example where I’ve moved the data 4 columns to the right, column E is now column index number 1, column F is 2 and column G is 3.

Column A, B, C, D, and all the other columns to the right of the data are now not considered a part of the data and have no column index numbers.

Got it? Great!

Going back to our original data in the sample file, you want to return the salary which is located in column index number 3.

Simply type 3 in your formula and move on to the next step by typing a comma.

Your formula should now look like this.

Column index number 3 is inserted in the VLOOKUP formula and it

Let’s move on to the next step.

Step 5: Do you want to be precise or approximate?

When we look for something like a name (Nate Harris) and want to see his salary we don’t want to find a Nathan Jones or a Nate Miller instead just because their names are close to each other’s.

Excel deals with these 2 terms:

“Approximate match” “Exact match”
Used if you’re looking for a value that is closest to your lookup value. Used if you’re looking for a value that is equal to your lookup value.

It’s very easy to choose whether to use EXACT or APPROXIMATE match.

Just pick “FALSE” from the helper menu that pops up when you’ve entered the comma from step 4 – alternatively, you can type ‘FALSE’ after the comma.

Your formula should look like this by now.

Almost finished VLOOKUP function. We

Rule of thumb: Always pick “exact match” when using VLOOKUP

But rules are made to be broken right?

The exception: When you’re looking for a value inside an interval you can use VLOOKUP with an approximate match.

This is better illustrated by using an example, so let’s imagine a dataset like this:

The exception: When to use “approximate match”

A VLOOKUP function with approximate match. Example.

When the lookup value is 15 (which it is in the picture) the ware status in cell E4 (where the formula is) shows ‘Very low’.

In this example, the values are categorized like this:

  • 1-19 equals a very low status.
  • 20-39 equals a low status.
  • 40-59 equals medium.
  • 60-79 equals high.
  • 80+ equals very high.

In this case, you can’t use a VLOOKUP with EXACT match since this would only work when the quantities 0, 1, 20, 40, 60 and 80 are entered in the lookup value cell (E4).

With the data set up in intervals like this, you want to use VLOOKUP with approximate match (in almost every other scenario you should go for the exact match).

Great, so that is when you should use “approximate match” instead of “exact match”.

Now, let’s continue with the primary example of this article.

Let’s put a parenthesis and get on with our formula.

It should look like this:

A completed VLOOKUP formula with exact match.

It’s time to move on to the last step…

Step 6: Press ‘Enter’

What do we usually do when completing a formula?

We press enter.

And that’s exactly what you’re going to do now!

This is what happens when there

When you’ve pressed “Enter” we don’t know if the formula is working or not.

We have to enter something the formula can look for in the ‘lookup value cell’ (F2) to validate what we’ve done is correct (which is why you’re currently seeing a #N/A error).

At the #N/A error, click the small exclamation mark left to the cell and you can see that Excel calls this a ‘Value Not Available Error’.

When we haven’t entered anything as the lookup value, Excel is automatically looking for ‘nothing’ in the left column of the table array.

When it can’t find anything (there’re only cells that contain something in our table array), it simply returns an error that states that it can’t find what you’re looking for.

Let’s try and type Nate Harris into our ‘Name’ cell (F2) and see what happens…

After we enter a name in the lookup value cell, then the VLOOKUP function returns our fictional persons salary.

Congratulations!
You’ve now made a tool to search for a person’s salary in an employee table.

Try to enter other people’s names in the ‘lookup value’ cell (F2) and witness the power of your newly created tool.

So, that’s how you create a VLOOKUP from scratch!

Make sure to bookmark this tutorial and go through the 6 steps next time you create a VLOOKUP function.

So, now that you can do a VLOOKUP, check out…