Microsoft Office Tutorials and References
In Depth Information
Understanding lookup and reference functions
TABLE 14-3 LOOKUP function arguments
The value, cell reference, or text (enclosed in quotation marks) you
want to find in a table or a range.
A cell range or name that defines the table in which to look.
The row or column number in the table from which to select the
result, counted relative to the table (not according to the actual row
and column numbers).
A logical value that determines whether the function matches
lookup_value exactly or approximately. Type FALSE to match lookup_
value exactly. The default is TRUE, which finds the closest match.
A one-row or one-column range that contains numbers, text, or
A one-row or one-column range that must be the same size as
A range containing numbers, text, or logical values to compare with
The array form of LOOKUP determines whether to search horizontally or vertically based on
the shape of the table defined in the array argument. If the table has more columns than
rows, LOOKUP searches the first row for lookup_value ; if the table has more rows than
columns, LOOKUP searches the first column for lookup_value . LOOKUP always returns the last
value in the row or column containing the lookup_value argument, or you can specify a row
or column number using VLOOKUP or HLOOKUP.
The VLOOKUP and HLOOKUP functions
For the VLOOKUP and HLOOKUP functions, whether Excel considers a lookup table to be
vertical or horizontal depends on where the comparison values (the first index) are located.
If the values are in the leftmost column of the table, the table is vertical; if they are in the
first row of the table, the table is horizontal. (In contrast, LOOKUP uses the shape of the
table to determine whether to use the first row or column as the comparison values.) The
comparison values can be numbers or text, but they must be sorted in ascending order. No
comparison value should be used more than once in a table.
The index_num argument (sometimes called the offset ) provides the second index and tells
the lookup function which column or row of the table to look in for the function’s result.
The first column or row in the table has an index number of 1; therefore, the index_num
argument must be greater than or equal to 1 and must never be greater than the number
of rows or columns in the table. For example, if a vertical table is three columns wide, the
index number can’t be greater than 3. If any value does not meet these rules, the function
returns an error value.