Microsoft Office Tutorials and References
In Depth Information
Understanding lookup and reference functions
The LOOKUP function
The LOOKUP function is similar to VLOOKUP and HLOOKUP, follows the same rules, and
is available in the same two forms, vector and array , whose arguments are described in
Like HLOOKUP and VLOOKUP, the vector form of LOOKUP searches for the largest
comparison value that isn’t greater than the lookup value. It then selects the result from the
corresponding position in the specified result range. The lookup_vector and result_vector
arguments are often adjacent ranges, but they don’t have to be when you use LOOKUP.
They can be in separate areas of the worksheet, and one range can be horizontal and the
other vertical. The only requirement is that they must have the same number of elements.
For example, consider the worksheet in Figure 14-4, where the ranges are not parallel.
Both the lookup_vector argument, A1:A5, and the result_vector argument, D6:H6, have five
elements. The lookup_value argument, 3, matches the entry in the third cell of the lookup_
vector argument, making the result of the formula the entry in the third cell of the result
The array form of LOOKUP is similar to VLOOKUP and HLOOKUP, but it works with either a
horizontal table or a vertical table, using the dimensions of the table to figure out the
location of the comparison values. If the table is taller than it is wide or the table is square, the
function treats it as a vertical table and assumes that the comparison values are in the
leftmost column. If the table is wider than it is tall, the function views the table as horizontal
and assumes that the comparison values are in the first row of the table. The result is always
in the last row or column of the specified table; you can’t specify column or row numbers.
Figure 14-4 The vector form of the LOOKUP function can retrieve information from a
nonparallel cell range.
Because HLOOKUP and VLOOKUP are more predictable and controllable, you’ll generally
find using them preferable to using LOOKUP.