| |
LOOKUP
Function
One of
the more powerful functions available in Excel is the LOOKUP
function. This allows you to take any value entered, find
it in a designated table, then return a value from that same
table. It is very practical, for example, if you wish to look
up a student's name by typing in the social security number.
In the Paste Function setup, there are actually LOOKUP functions
for a list that is in a vertical table and a horizontal table.
(called VLOOKUP and HLOOKUP, respectively.) This sample spreadsheet
will be used to demonstrate the VLOOKUP function.
Notice
the table at the left side. This table contains the data we
will look up when entering numbers into column E. To make
things easier, we can highlight cells A1:C6 and give the range
a NAME. Clicking in the Name Box and typing in a name will
accomplish this. <Click
here> to see a video demo of naming a range of cells.
(128K)
In theory,
if we type 100 into cell E5, then cell F5 should return the
result "Snicker's," and cell G5 should return $0.55.
We begin by clicking on cell F5 and using the Paste Function
button to choose the VLOOKUP function. 
The VLOOKUP
function has four arguments, three of which are required.
The arguments
are:
- Lookup_value:
This is the cell that contains the value we want to research.
In this situation, it's cell E5.
- Table_array:
The range of cells that contain the table where the value
is. The value must be in the first column. The table is
the one we created in the Range Names section: CandyTable
- Col_index_num:
Once VLOOKUP finds the number in the first column, it must
return the value from the same row in a different column.
The columns are numbered in this case. DO NOT GO BY THE
LETTER NAME OF THE COLUMN. The value here is 2, since we
want the name of the candy bar returned.
- Range_lookup:
This is an optional True/False value, which asks if an approximate
match is acceptable. Leaving it blank or saying TRUE allows
an approximate match, saying FALSE then requires an exact
match.
Two things
to note. Firstly, the values in the first column must be sorted
ascending, or the LOOKUP will not work. Secondly, since Range_lookup
will be left blank, if the Lookup Value is not found, the
computer will choose the next lowest value. Here is what the
screen looks like when the values are entered. <Click
here> to see a video demo of the LOOKUP function in
action. (218K)
|
