XL97: Range_Lookup Argument in VLOOKUP() Finds Exact Match (108639)

   =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

   =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
				

MORE INFORMATION

VLOOKUP()

The following table outlines the definition for each argument in the VLOOKUP() function:

   Argument     Definition of argument
   ---------------------------------------------------------------------

   lookup_value The value to be found in the first column of the array.

   table_array  The table of information in which data is looked up.

   col_index    The column number in the table_array for which the
                matching value should be returned.

   range_lookup This is a new argument used in Microsoft Excel versions
                5.0 and later. It is a logical value that specifies whether
                you want to find an exact match or an approximate match.
                If TRUE or omitted, an approximate match is returned; in
                other words, if an exact match is not found, the next
                largest value that is less than the lookup_value is
                returned. If FALSE, VLOOKUP() will find an exact match.
                If an exact match is not found, the #N/A error value is
                returned.
				

NOTE: If range_lookup is TRUE or omitted (that is, an approximate match), the values in the first column of table_array must be sorted in ascending order. If range_lookup is FALSE (that is, an exact match), the table_array does not need to be sorted.

HLOOKUP()

The following table outlines the definition for each argument in the HLOOKUP() function:

   Argument     Definition of argument
   ---------------------------------------------------------------------

   lookup_value The value to be found in the first row of the array.

   table_array  The table of information in which data is looked up.

   row_index    The row number in the table_array for which the
                matching value should be returned.

   range_lookup This is a new argument used in Microsoft Excel versions
                5.0 and later. It is a logical value that specifies whether
                you want to find an exact match or an approximate match.
                If TRUE or omitted, an approximate match is returned; in
                other words, if an exact match is not found, the next
                largest value that is less than the lookup_value is
                returned. If FALSE, VLOOKUP() will find an exact match.
                If an exact match is not found, the #N/A error value is
                returned.
				

NOTE: If range_lookup is TRUE or omitted (that is, an approximate match), the values in the first row of table_array must be sorted in ascending order. If range_lookup is FALSE (that is, an exact match), the table_array does not need to be sorted.

In earlier versions of Microsoft Excel, the INDEX() and MATCH() functions must be used to find an exact match in the table_array.

For additional information about using the VLOOKUP function in earlier versions of Microsoft Excel, please see the following article in the Microsoft Knowledge Base:

   compare lookup return