Microsoft KB Archive/108639

= XL97: Range_Lookup Argument in VLOOKUP Finds Exact Match =

Article ID: 108639

Article Last Modified on 7/18/2007

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 for Macintosh
 * Microsoft Excel 5.0a for Macintosh
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 95 Standard Edition

-



This article was previously published under Q108639





SUMMARY
In Microsoft Excel versions 5.0 and later, the VLOOKUP and HLOOKUP functions contain an argument called range_lookup. The syntax of these functions are different than in earlier versions and are defined as follows =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)



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.

