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.
Search JabSto ::




Custom Search