How to Use VLOOKUP

by Matthew Kuo on December 19, 2012

in Database Theory, Excel

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

Vlookup1

Understand the Objective

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

  1. To look up a value or a series of values from a large database
  2. 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”.

Vlookup2

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.

Vlookup3

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.

Vlookup4

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”.

Vlookup5

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.

Vlookup6

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:

  1. Starting from the top of the leftmost column of your table array, it searches for your lookup value
  2. 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
  3. After moving right by the specified number of columns, it returns whatever value it lands on

Vlookup1

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.

{ 14 comments… read them below or add one }

Gerald Herbaugh March 10, 2013 at 12:35 am

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.

Reply

Matthew March 10, 2013 at 12:48 am

Thanks for the catch.

Best,
Matthew

Reply

Joe March 12, 2013 at 8:56 am

Great tutorial! This helped a lot!

Reply

Sashankan March 13, 2013 at 5:54 am

Thanks for the Tutorial

Reply

erusa goundan April 4, 2013 at 11:54 pm

i like the vlookup

Reply

Pragya Verma April 13, 2013 at 9:54 am

very nice

Reply

Ivan May 23, 2013 at 2:15 am

Very well explained! 10x a lot!

Reply

krishna June 2, 2013 at 11:33 pm

thanks

Reply

www.afpcc.org June 7, 2013 at 8:26 am

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

Reply

JOHN June 12, 2013 at 5:32 am

THANKS SO MUCH 4 HELPING MY KNOWLEDGE VAST ON VLOOKUP

Reply

ramesh July 5, 2013 at 4:31 am

Thanks 100*time

Reply

Falakiko Asisi Pouhila September 5, 2013 at 6:23 pm

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

Reply

David Jone November 7, 2013 at 3:45 pm

Great job Matthew! Thanks for the explaining in detail!

Reply

Gareth November 4, 2014 at 1:26 am

Just a note to say great articles on here regarding lookups but the minimum index number is 1 not 2. I often find it useful to look up the same value.

Reply

Leave a Comment

{ 6 trackbacks }

Previous post:

Next post: