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