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