MS-Excel / Excel 2003

Performing a vertical table lookup

Excel supports two types of lookup functions, vertical and horizontal. The vertical lookup function is called VLOOKUP. The VLOOKUP function searches vertically (top to bottom) the first (leftmost) column of a lookup table until the program locates a value that matches or exceeds the one you're looking up.

The VLOOKUP function uses the following syntax:

VLOOKUP(lookup_value,table_array,
colindex_num,[range_lookup])

Here's a closer look at the arguments that make up this function:

  • lookup_value: The value that you want to look up and match in the first column of the lookup table.
  • table_array: The cell range or name of the lookup table itself.
  • col_index_num: The column containing the values that you want returned to the cell containing the VLOOKUP formula when a match is made between the lookup_value and an entry in the first column of the table. When entering this argument, you must enter a value greater than zero that doesn't exceed the total number of columns in the lookup table.
  • range_lookup: (Optional) The logical TRUE or FALSE value that specifies whether you want Excel to find an exact or approximate match for the lookup_value in the table_array. When you specify TRUE or omit the range_lookup, Excel finds an approximate match. When you specify FALSE as the range_lookup argument, Excel finds only exact matches.

Finding approximate matches pertains only when you're looking up numeric entries (rather than text) in the first column or row of the vertical or horizontal lookup table. When Excel doesn't find an exact match in this lookup column or row, it locates the next highest value that doesn't exceed the lookup_value argument and then returns the value in the column or row designated by the col_index_num or row_index_num arguments.

[Previous] [Contents] [Next]