VLOOKUP Function Hints for Spreadsheets

Steps to do a VLOOKUP:

The steps to do a lookup are simple but MUST be followed exactly and in this order:

Create the table
Name the Table
Write the VLOOKUP function

VLOOKUP in Excel 2007, 7/26/7 9:49

Create the table (see Example Below)

On an isolated part of the sheet (way over to the right ) or on a separate sheet (see tabs on bottom of screen) enter the table.

The first column has the value that are to be compared to.

	Sometimes the match is exact, like if state abbreviations were in column 1.
	Sometimes the match is for the largest number that is less or equal to that the number driving the search, like when looking for a tax rate based on income.
	Be sure the values in column 1 are in order: lower numbers first or alphabetical order. To make sure, highlight the table and Excel 2003 : Data menu, Sort, Excel 2007: Data Ribbon, Sort
	Column 1 can ONLY have numbers (or exact words (groups of characters like state abbreviations) to be match to.) The table must be usable in France and Greece and Portugal and China. Excel does NOT understand the words "over" or "less than" or other English.

The next column(s) has the result to be returned when a match is found.

Optionally the table name or column names can be entered above the table,
but these for human clarification and are NOT part of the table.

Name the table

Highlight the table (just the table, not its name nor column names).

Excel 2003: Name it (Insert menu, Name, Define, type a name). No spaces in names.

Excel 2007: Name it either

	right click and select Name a Range .. or
	Formulas Ribbon, Define

It is essential to name the table if you intend to drag the formula down the row so the table's address is absolute (does not change as copies go to adjacent cells).
Give it a VERY short name like "tax" or just "T" since you can type that name later.

Write the VLOOKUP (see Example Below)

Go to the cell where the answer is to go.

Enter the VLOOKUP Formula

Use the

(function) icon (find VLOOKUP either in Recently Use or Lookup or All category)

Move the dialog box sp the values can be seen

The 1^st argument is what is being looked up (what value Excel is to look for as it scans the table).
Click in the one cell where the value is. That cell address or name should appear in the first box and the value should appear to the right. Note the value that you are trying to match to the 1st column of the table MUST be in a cell by all by itself.

The 2^ndparameter is the table. Either

	highlight it or
	tap function key F3 and select the table name or
	type its name.

The table name should appear in the 2nd box and its first few values in the table appear to the right.

The 3^rdparameter is what column of the table contains the answer.
For the 2^nd column, type 2.

The 4^th argument is not bold and is left blank unless you have an unusual lookup..

Example of Creating a table

For example,
if instructed to look up a discount as follows:

under $10	none
$10 to $99.99	10%
$100 to $499.99	20%
over $500	25%

The table would be entered as

0	0
10	10%
100	20%
500	25%

Excel interprets this as (the table is read: )

	If the number you are looking up is from 0 to 9.99999, return 0,
	if it is from 10 to 99.999999999, return 10%,
	if it is 100 to 499.9999999999, return 20%
	and everything 500 and over, return 25%.

Rules for tables:

	Each table cell can have only one value Excel does NOT understand English (nor Spanish, nor French,...) so column 1 can contain ONLY numbers. "10-99" is INVALID! "over 100" is INVALID!
	Tables should start with 0 when there might be values less than the first value
	If column 1 contains words, the cells must be in alphabetical order

Sample VLOOKUP

For the problem
look up a discount as follows:

under $10	none
$10 to $99.99	10%
$100 to $499.99	20%
over $500	25%

First create a table like below

Discount

0 0
Discount is read:

From 0 to $10, no discount

Over $10 and up to $99.99, 10%

100 to 499.99 yields 20%

and over $500, 25%

10 10%

100 20%

500 25%

Then name it
Then Create the VLOOKUP

what are you looking up?	B2
where is the table?	discount
which column has the answer?	2

The three blocks for VLOOKUP
would be entered:

the table (cells A5 to B7) and lookup formula in the sheet would look like this

	A	B
1	Price	$70
2	Discount	=VLOOKUP(B1,discount,2)
3	You Pay	=B1-B2

Use the Excel Help Menu and find VLOOKUP in the Index or
check out these web site for explanations/examples:

	http://www.contextures.com/xlFunctions02.html
	http://www.uic.edu/depts/accc/seminars/excel2000-intermed/lookup.html
	http://www.ptti.com/html/help/VLOOKUP_.htm

[ Home ] [ References ] [ In the News ] [ Cyber-Briefcase ] [ Learning Pyramid ] [ Photos of Students ] [ CIS100 Comp. Apps ] [ The Internet - CIS120 ] [ Web Page Development ] [ Tips and Tricks ] [ Job Search ] [ Spring '08 Schedule ] [ Tutorial Sites ] [ Hints for Printing ] [ About M.L.G. ] [ Disclaimer ][ YouTube ] [ VLOOKUP Function Hints ] [ IF Function Hints ] [ Cell Names & Reference ] [ IF & Naming Quiz ] [ PMT Function Hints ]

This page was created by and was last updated on 09/15/07

Page Name: Hints: VLOOKUP Function
URL: http://faculty.ucc.edu/business-greenbaum/C100SS_HVLOOKUP.htm

Disclaimer: Information on this site represents the thoughts and opinions expressed by the author and not that of Union County College. The author takes full responsibility for the information presented. By using the information contained herein, the user willingly assumes all risks in connection with such use. Neither the author nor Union County College shall be held liable or responsible for content, errors, and/or omissions in information herein or information contained on any Web Page to which it is linked. Furthermore, neither the author nor Union County College shall be liable for any special, consequential, or exemplary damages resulting, in whole or in part, from any user's use of, or reliance on, this material or material set forth on any Web Page to which it is linked.
Copyright: ã Maureen Greenbaum Sumware in NJ 2001, '02, '03, '04, '05, '06, '07, 2008. All rights reserved.