For most Excel users, VLOOKUP is the first complex formula they learn. Performing a lookup is an extremely valuable function for any situation where you’re dealing with large data sets. While the VLOOKUP function itself has become somewhat dated, as there are other better alternatives such as INDEX MATCH, it is still important to learn this function because:

- VLOOKUP is probably the simplest lookup formula of the ones available and if you have no prior spreadsheet experience, it should be the formula you start off with
- Despite being dated, many people still use VLOOKUP in their spreadsheets and if you ever end up auditing someone else’s spreadsheet, you’ll need to know how this formula works

Click here for a detailed explanation of why INDEX MATCH is better than VLOOKUP

**Understand the Objective**

The first step to learning VLOOKUP is knowing when to use it. VLOOKUP is used for two primary purposes:

- To look up a value or a series of values from a large database
- To append a column to a table using data from a larger database

The process for using VLOOKUP doesn’t change much between the two objectives, so we’ll focus on the first objective. In the example below, we have a value called **ID** circled in blue and a table of data circled in green. The cell highlighted in yellow is the return value, where we are missing a value for **State**. Essentially, we want to know which **State** is relevant to **ID** number “5”.

**Syntax**

After understanding your objective, it’s important to learn the **syntax**, or inputs required, to write out the VLOOKUP formula. The syntax for VLOOKUP has changed over time to make it simpler and easier to use. In the latest version of Excel (2010 at the time of this writing), the syntax for a VLOOKUP automatically appears once you type in the formula and an open parenthesis. We’ll look at each component individually.

=VLOOKUP ( **lookup value **, **table_array **, **col_index_num **, **[range_lookup] **)

**Lookup value**

The lookup value is the value we need more information on. In the example below, this is the **ID** number “5”. We will start by typing our formula in the blank cell where we want to return the missing information. Then we select the lookup value for the first component of the syntax and follow it with a comma.

**Table Array**

The table array is larger data set where your return value exists. In most cases this dataset will be large enough that you’ll want to use a formula to pull the value, rather than looking it up manually. Once you become proficient at VLOOKUP, it also reduces the possibility of making a mistake.

To select your table array, simply click and drag around the entire data set that is relevant to your lookup formula. And again, complete the entry of this component by inputting a comma.

**Column Index Number**

This component simply references which column you want to pull back your data from. It’s important to note that the minimum number for this input is 2 – you cannot look up and return the same value (as doing so probably doesn’t make much sense). The maximum value depends on how large your table array is. In the example below, we only have 5 total columns. Therefore, our maximum input for column index number is 5. Inputting any number higher than 5 will return an error value.

In the example below, since we want to return the **State** related to our **ID**, and **City** is the fourth column in the table array we selected, we input the number “4”.

**Range Lookup**

Few people actually know what a range lookup does, because range lookup is a functionality that few people ever use. The range lookup simply tells your lookup formula to look for values that are close to your lookup value, not ones that are exactly the same. The rules on what’s “close to” your lookup value are vague and if you’re working on a project where you need some level of precision, you can see why this functionality can be problematic. You basically have three options to deal with the range lookup portion of the syntax:

1. Choose not to use range lookup

To choose this option, input either a “0” or the word “FALSE”. Many of us who’ve used this formula for years are used to typing in FALSE; the option to put in a 0 is relatively new.

2. Choose to use a range lookup

To turn on the range lookup functionality, simply do the reverse of the prior inputs, and enter either “1” or “TRUE”.

3. Ignore it

If you simply close off the parenthesis and leave out this final piece of syntax, the formula will default it to “TRUE” and have your VLOOKUP perform a range lookup.

For our particular example, because we want an exact match, we will input “0” so the VLOOKUP does not perform a range lookup. After you’ve written your formula with your desired inputs, close the formula with a “)” and press ENTER to finish writing it.

**What Excel Does**

If you’ve written the formula properly, the value you were looking for will be returned in the lookup result cell. After you’ve written all of your parameters, the formula performs a vertical lookup:

- Starting from the top of the leftmost column of your table array, it searches for your lookup value
- Once it finds your lookup value, it stops moving down and begins moving over to the right based on the column index number you specified
- After moving right by the specified number of columns, it returns whatever value it lands on

If your lookup result cell comes up with an error, there are a number of reasons why this might have occurred. Over time you will learn how to troubleshoot your errors.

**Summary**

After you’ve mastered using VLOOKUP, you’ll be able harness one of the primary uses of Excel spreadsheets. However, you should not stop this formula; there are other lookup formulas that are superior to VLOOKUP and provide additional functionality. In particular, INDEX MATCH is now the preferred method to perform this type of lookup.

{ 13 comments… read them below or add one }

In the paragraph “Column Index Number”, there is a significant error in the following sentence: “In the example below, since we want to return the City related to our ID, and City is the fourth column in the table array we selected, we input the number “4″. In fact it is State which we want to return in the example and which is in column 4, not City.

Thanks for the catch.

Best,

Matthew

Great tutorial! This helped a lot!

Thanks for the Tutorial

i like the vlookup

very nice

Very well explained! 10x a lot!

thanks

Hello, just wanted to mention, I loved this post. It was practical. Keep on posting!

THANKS SO MUCH 4 HELPING MY KNOWLEDGE VAST ON VLOOKUP

Thanks 100*time

I have question on using excel function

1. The advance of using spreadsheet

2. The combination of column letter and the row number is:

3. What is the formula would you use to find the youngest student from a range of age?

4. True or False

– The IF function can only test for true condition

– You can create text boxes by placing text inside rectangular graphic objects

– The goal seek command can seek a specific solution to a formula

– Excel doesn’t allow you to protect you data

– You can hide your excel’s columns and rows to protect information from

being seen

5. Please clarify the difference between relative reference, absolute

reference and mixed reference

6.List 3 errors you might come across while working in Excel

Great job Matthew! Thanks for the explaining in detail!

{ 6 trackbacks }