Microsoft Office Tutorials and References
In Depth Information
Using Other Functions
The Range, by the way, needs to include the
column/row that contains the labels or
everything will be thrown off. The ColumnNumber/
RowNumber is the column/row where the data
you’re trying to look up is stored. For example,
if you’re trying to look up an employee’s phone
number, and that phone number is in column
5 of the Range, you would use 5 as your
ColumnNumber argument.
Tip
Excel has a special function EDATE, for
adding or subtracting so many months
from a date, which you can use instead
of the method described here. The syntax
is =EDATE(StartDate,Months) as in
=EDATE(E4,3).
Finally, the Type is optional, but if you omit it,
Excel assumes “True,” which means that your
database is sorted by the ItemToFind
column/row, and that you will accept an answer
that’s close but not over if the actual item can’t
be found. For example, suppose you are looking
up an employee by his ID, and that the Range is
sorted by ID, from lowest to highest. If you tell
Excel to look up ID number 417 and there isn’t
one, Excel will look up the closest ID that isn’t
over 417 (such as say, 415) and give you the
results for that person. If you set Type to False,
Excel will only provide an answer if an exact
match is found. Also, if you set Type to False,
Excel does not assume that the Range is sorted
by ID (to continue this example).
Using Lookup and Reference
Functions
The Lookup and Reference functions are used to
locate data in a large database of information,
such as an employee or inventory database. The
two most common of these functions include
VLOOKUP and HLOOKUP.
VLOOKUP and HLOOKUP
Both VLOOKUP and HLOOKUP are used to look
up information in a database. The VLOOKUP
function looks up the data vertically down a
particular column, whereas HLOOKUP looks up
data horizontally across a particular row. Here is
the syntax:
Consider the worksheet shown in Figure 3-19,
which shows an employee database for
PhotoTown. Because the database is set up
vertically, with the labels across the top of each
column, you need to use VLOOKUP to look up
an employee’s emergency phone number when
needed. You’ve planned the worksheet so that
you can enter the employee’s name in cell I4, and
his/her phone number appears in cell I5. Type
=VLOOKUP(I4,C7:H37,6,FALSE). Cell I4 has the
value to look up, which in this case is the
employee’s name. The range C7:H37 has the data—note
that column C contains the employee names.
=VLOOKUP(ItemToFind,Range,ColumnNumber,Type)
=HLOOKUP(ItemToFind,Range,RowNumber,Type)
The ItemToFind is a cell address, formula, or the
actual value you are trying to find in the
database, such as a particular employee. This item
must be contained in the first column/row of
the Range you specify. So, if you’re looking up
an employee using his ID, the ID needs to be
the first column or row of your database Range.
 
Search JabSto ::




Custom Search