VLOOKUP Formulas in Microsoft Excel
The following graphic provides a
breakdown for a VLOOKUP formula, and describes each argument. It may be helpful
to review it before you continue reading the rest of the article. The V in
VLOOKUP means "vertical". HLOOKUP works similarly, but finds data going across
columns, instead of down rows.
VLOOKUP has many purposes. You
can quickly create a pricelist that is thousands of rows long on Sheet2. On
Sheet1, you can designate a cell into which to type a part number, and then
have a VLOOKUP that finds the price on Sheet2. No scrolling, no need to use
the Find feature to find the price for the part.
The following graphic demonstrates how to look up the salary of only several
employees. Note that the VLOOKUP formula in cell D2 is shown in the formula bar.
So, what's MyLookup? That's our data range or database" where the Salary table
lies, which is demonstrated in the following graphic.
In the above, columns A through G were selected, and a named range was created
by hitting InsertàNameàDefine,
and typing MyLookup.
People often get errors when performing VLOOKUPs.
Here's a few common errors and the reasons for them.
When you get this error, it means that the formula is not finding the value that
you're looking for in the list in which you're looking. In our example, that
means that the value in A2 was not found in the first column of the Employee
Data sheet. Unfortunately, you may also get this error if you are looking for a
number, and your numbers are formatted as text. You can test this theory: Create
a new workbook. Format column A as text. Format column B as a custom format with
000000 as the custom format. Now, type '000123 in cell A1 (don't miss the
apostrophe before the first zero there) and type 123 in B1. Both values *look*
the same. However, you have 7 characters in cell A1 and only 3 characters in B1.
Trying to do a VLOOKUP under these circumstances will not work. This happens
often when you get data from an *external data source*, such as a mainframe
database. Basically, VLOOKUP works best when the values you're looking for and
the values you're looking in both have the same format. To repair this, you must
do the following procedure to one or both of the data ranges of numbers. We call
it *The Copy Blank Fix* for lack of a better title. The Copy Blank fix.
Copy any blank cell. Select the range of numbers or dates that may be
causing the error. Hit EditàPaste special, and
choose Add. Hit OK. Your range will likely lose its formatting and need to be
formatted again. However, this FORCES Excel to see the data as numbers or date
values instead of as text.
Wrong Value Returned
This is usually caused by not including the FALSE (or fourth) argument in the
VLOOKUP formula. If your data lookup column is not sorted, your formula may find
the closest value it first comes across, unless you use the FALSE statement. One
of the few times a FALSE statement should NOT be used is if you have a range of
values that relate to another range of values. Suppose we have insurance
premiums that are $1.00 per month up to age 16, $2.00 per month up to age 21,
and so on. You might have the first column be the ages, i.e., 16, 21, 25, etc.
Now suppose the value you are looking up is 20. It should then return the
nearest match without going over, which would be the same amount as for a lookup
value of 16, or $1.00 in this case. Change the value you are looking up to 22
and it will find the 21-year-old value.
This is a common issue. Our VLOOKUP sample shows how to create the formula using
named ranges. However, probably more commonly used is the referenced range. In
this case, it would be A1:G7 of the Employee Data sheet. So instead of
=VLOOKUP(A2,MyLookup,2,FALSE) our formula would read =VLOOKUP(A2,Employee
Data!$A$1:$G$7,2,FALSE). Many people forget to put the dollar signs in to keep
the range ABSOLUTE. In other words, when they copy the formula down one cell,
instead of getting: =VLOOKUP(A3,Employee Data!A1:G7,2,FALSE) , they get
=VLOOKUP(A2,Employee Data!A2:G8,2,FALSE) because they did not use the dollar
signs. This is why it is so much easier to use a named range. And also to use
entire columns when naming the range.
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved