5. VLOOKUP and HLOOKUP functions
The functions VLOOKUP and HLOOKUP are used to find a value in a table according to a key value.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Arguments
lookup_value : is the value to be searched in the first column of the table_array range in the case of the VLOOKUP function. This is the value to be searched in the first row of the table_array range in the case of function HLOOKUP
table_array : is a range of cells in table form where the search will take place. The value to be retrieved must be in the first column (in the case of VLOOKUP) or in the first row (in the case of HLOOKUP). This range must also contain the value to be retrieved
col_index_num : is the column number in table_array where the value corresponding to the searched value is retrieved
row_index_num : is the row number in table_array where the value corresponding to the searched value is retrieved
range_lookup : if this argument is omitted or has the value true, then the match between the searched value and the value in table_array need not be exact
Result
Case of VLOOKUP function : the value returned is that of the cell on the same row as the cell where the searched value is found. And on the column number col_index_num in the table_array
Case of HLOOKUP function : the value returned is that of the cell on the same column as the cell where the searched value is found. And on the row number row_index_num in the table_array
Example
The formula written in B12 is : "=VLOOKUP(A12,A3:H8,8,FALSE)"
The function VLOOKUP searches for the value written in A12 which is "Bristol" in the first column of the range A3:H8. This value is found in row 7. The value returned is that of the cell intersection of row 7 and the 8th column of the range A3:H8.
The formula written in H12 is : "=HLOOKUP(G12,B2:G9,8,FALSE)"
The function HLOOKUP searches for the value written in G12 which is "Trading" in the first row of the range B2:G9. This value is found in column F. The value returned is that of the cell intersection of column F and the 8th row of the range B2:G9.
Exercises
Exercise – VLOOKUP and HLOOKUP functions