Microsoft Office Tutorials and References

In Depth Information

**Looking up information in a worksheet**

The
VLOOKUP
function finds a value in the leftmost column of a named range, such as a

table, and then returns the value from the specified cell to the right of the cell that has the

found value. A properly formed
VLOOKUP
function has four arguments (data that is passed

to the function), as shown in the following definition:
=VLOOKUP(lookup_value, table_array,

col_index_num, range_lookup)
.

The following table summarizes the values Excel expects for each of these arguments.

Argument

Expected value

The value to be found in the first column of the named range specified by

the
table_array
argument. The
lookup_value
argument can be a value, a

cell reference, or a text string.

lookup_value

The multicolumn range or name of the range or Excel table to be

searched.

table_array

The number of the column in the named range that has the value to be

returned.

col_index_num

A
TRUE
or
FALSE
value, indicating whether the function should find an

approximate match (
TRUE
) or an exact match (
FALSE
) for the
lookup_

value
. If left blank, the default value for this argument is
TRUE
.

range_lookup

IMPORTANT
When
range_lookup
is left blank or set to
TRUE
, for
VLOOKUP
to work properly

the rows in the named range specified in the
table_array
argument must be sorted in ascending

order based on the values in the leftmost column of the named range.

The
VLOOKUP
function works a bit differently depending on whether the
range_lookup

argument is set to
TRUE
or
FALSE
. The following list summarizes how the function works

based on the value of
range_lookup
:

▪
If the
range_lookup
argument is left blank or set to
TRUE
, and
VLOOKUP
doesn’t find

an exact match for
lookup_value
, the function returns the largest value that is less

than
lookup_value
.

▪
If the
range_lookup
argument is left blank or set to
TRUE
, and
lookup_value
is smaller

than the smallest value in the named range, an
#N/A
error is returned.

▪
If the
range_lookup
argument is left blank or set to
TRUE
, and
lookup_value
is larger

than all values in the named range, the largest value in the named range is returned.

▪
If the
range_lookup
argument is set to
FALSE
, and
VLOOKUP
doesn’t find an exact

match for
lookup_value
, the function returns an
#N/A
error.