Microsoft Office Tutorials and References

In Depth Information

If you know a shipment’s ShipmentID, it’s no trouble to look through a list of 20 or 30

items to find a particular shipment. If, however, you have a list of many thousands of

shipments, looking through the list to find one would take quite a bit of time. Instead,

you can use the
VLOOKUP
function to let your colleagues type a ShipmentID in a cell

and have the corresponding details appear in another cell.

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 with

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

lookup_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.

table_array
The multicolumn range or name of the range or data table to be searched.

col_index_num
The number of the column in the named range with the value to be

returned.

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.