Microsoft Office Tutorials and References
In Depth Information
Functions Relevant to Lookups
Figure 8-1: Lookup formulas in row 2 look up the information for the employee name in cell B2.
This particular example uses four formulas to return information from the EmpData range. In
many cases, you’ll only want a single value from the table, so use only one formula.
Functions Relevant to Lookups
Several Excel functions are useful when writing formulas to look up information in a table. Table
8-1 lists and describes each of these functions.
Table 8-1: Functions Used in Lookup Formulas
Function
Description
CHOOSE
Returns a specific value from a list of values (up to 254) supplied as arguments.
VLOOKUP
Vertical lookup. Searches for a value in the first column of a table and returns a
value in the same row from a column you specify in the table.
HLOOKUP
Horizontal lookup. Searches for a value in the top row of a table and returns a value
in the same column from a row you specify in the table.
INDEX
Returns a value (or the reference to a value) from within a table or range.
LOOKUP
Returns a value either from a one-row or one-column range. Another form of the
LOOKUP function works like VLOOKUP but is restricted to returning a value from
the last column of a range.
MATCH
Returns the relative position of an item in a range that matches a specified value.
OFFSET
Returns a reference to a range that is a specified number of rows and columns from
a cell or range of cells.
The examples in this chapter use the functions listed in Table 8-1.
Basic Lookup Formulas
You can use Excel’s basic lookup functions to search a column or row for a lookup value to return
another value as a result. Excel provides three basic lookup functions: HLOOKUP, VLOOKUP, and
 
Search JabSto ::




Custom Search