Returning Single Values from a Lookup Table

The most popular of the Lookup and Reference functions are the HLOOKUP (for Horizontal Lookup) and VLOOKUP (for Vertical Lookup) functions. The VLOOKUP function searches vertically (top to bottom) the leftmost column of a lookup table until the program locates a value that matches or exceeds the one you are looking up. The HLOOKUP function searches horizontally (left to right) the topmost row of a lookup table until it locates a value that matches or exceeds the one you are looking up.

The VLOOKUP function uses the following syntax:

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

The HLOOKUP follows the nearly identical syntax:

HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

The arguments of these two Lookup functions can be explained as follows:

• The lookup_value argument designates the range that contains the values or text to be looked up in the table.
• The table_array argument designates the range with the data table you want looked up in the lookup table as well as the data you want returned from the lookup table.
• The col_index_num argument in the VLOOKUP function designates the number of the column in the lookup table (starting with 1 for the leftmost column and increasing one each column to the right) that contains the data you want returned to the data table.
• The row_index_num argument in the HLOOKUP function designates the number of the row in the lookup table (starting with 1 for the topmost row and increasing one down each row) that contains the data you want returned to the data table.
• The optional range_lookup argument is a TRUE or FALSE value that indicates whether you want Excel to find an approximate (TRUE or argument omitted) or exact match (FALSE) to numerical entries in the range designated by the function's lookup_value argument.

When using the VLOOKUP and HLOOKUP functions, the text or numeric entries in the lookup column or row (that is, the leftmost column of a vertical lookup table or the top row of a horizontal lookup table) must all be unique (no duplicates allowed). These entries must also be arranged or sorted in ascending order; that is, alphabetical order for text entries, lowest-to-highest order for numeric entries.