VLOOKUP function in excel an extremely valuable function

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

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

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 theID 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.  Ignore it

Since it isn’t a popular function, the latest version of Excel allows you to simply leave out this last piece of syntax.  Therefore, instead of entering anything for this, you can simply input a close parenthesis and finish writing the formula.

2.  Choose not to use range lookup

To choose this option, input either a “0″ or the word “FALSE”.  This has the same effect as ignoring the last piece of the syntax; it just adds more keystrokes to your task.  However, many of us who’ve used this formula for years are so used to incorporating it that we always put this last piece of syntax in.  That’s why you see so many VLOOKUP formulas end in with the world FALSE.

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

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:

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

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.