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

Table 14-3.

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

range: 300.

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.