How to Use VLOOKUP or HLOOKUP to find an exact match (181213)

MORE INFORMATION

The syntax of these functions are defined as follows.

VLOOKUP Function

   =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
				

where:

   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 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 finds 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 (for an approximate match), the values in the first column of table_array must be sorted in ascending order. If range_lookup is FALSE (for an exact match), the table_array does not need to be sorted.

Example That Uses FALSE as the Range_lookup Argument

The following list contains some fruits and their respective colors. Notice that the first column is not sorted:

   A1: Fruit    B1: Color  
   A2: Kiwi     B2: Green 
   A3: Banana   B3: Yellow 
   A4: Grape    B4: Purple  
   A5: Apple    B5: Red   
				

The following formula finds the color (Red) that corresponds to the fruit Apple. You can type the formula in any cell on the worksheet:

=VLOOKUP("Apple",A2:B5,2,FALSE)
				

Notice that if you change the range_lookup argument to TRUE, Excel returns the #N/A error, because the first column is not sorted.

HLOOKUP Function

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
				

where:

   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.

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

   range_lookup 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 finds 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 (for an approximate match), the values in the first row of table_array must be sorted in ascending order. If range_lookup is FALSE (for an exact match), the table_array does not need to be sorted.

Example That Uses FALSE as the Range_lookup Argument

The following list contains some fruits and their respective colors. Notice that the first column is not sorted:

   A1: Fruit    B1: Color
   A2: Kiwi     B2: Green
   A3: Banana   B3: Yellow
   A4: Grape    B4: Purple
   A5: Apple    B5: Red
				

The following formula finds the Color column, and returns the third item (-1) for the heading Yellow. You can type the formula in any cell on the worksheet:

=HLOOKUP("Color",A1:B5,3,FALSE)
				

Notice that if you change the range_lookup argument to TRUE, Excel returns the #N/A error, because the first column is not sorted.