Here, we will learn how to use the Excel lookup and reference functions: HLOOKUP, LOOKUP and VLOOKUP.
HLOOKUP function
The Excel HLOOKUP function searches for the given value in the top row of the given array or range, and then returns the value in the same column using the given row number. For example, in the example shown, HLOOKUP searches for the item "Helium" in the range B3:F5 and then returns the item in the same column from the second row, that is 2. The function can be found in all the versions of Excel. HLOOKUP takes the arguments like this: (lookup_value, table_array, row_index, [range_lookup]), of which lookup_value, table_array and row_index are required. Lookup_value accepts the item to search for. Table_array accepts the array or range from which to retrieve data. Row_index accepts the row number from which to retrieve data. Range_lookup controls the exact match or approximate match. It is a Boolean argument that accepts either TRUE for an approximate match or FALSE for an exact match. The formula in I4, is:
=HLOOKUP(H4,B3:F5,2,FALSE)
LOOKUP function
The Excel LOOKUP function searches for the given item in the given one-column or one-row range, and then returns the item from the same position in another given one-column or one-row range. For example, in the example shown, LOOKUP searches for the value 94 in the range A9:A13 and then returns the item from the same position in the range B9:B13, that is "A". The function can be found in all the versions of Excel. LOOKUP takes the arguments like this: (lookup_value, lookup_vector, [result_vector]), of which lookup_value and lookup_vector are required. Lookup_value accepts the item to search for. Lookup_vector accepts the one-row, or one-column range to search in. Result_vector accepts the one-row, or one-column range from which to retrieve data. The formula in E10, is:
=LOOKUP(E9,A9:A13,B9:B13)
VLOOKUP function
The Excel VLOOKUP function searches for the given value in the left column of the given array or range, and then returns the value in the same row using the given column number. For example, in the example shown, VLOOKUP searches for the item "Boron" in the range A17:C21 and then returns the item in the same row from the third column, that is "B". The function can be found in all the versions of Excel. VLOOKUP takes the arguments like this: (lookup_value, table_array, column_index_num, [range_lookup]), of which lookup_value, table_array and column_index_num are required. Lookup_value accepts the item to search for. Table_array accepts the array or range from which to retrieve data. Column_index accepts the column number from which to retrieve data. Range_lookup controls the exact match or approximate match. It is a Boolean argument that accepts either TRUE for an approximate match or FALSE for an exact match. The formula in F17, is:
=VLOOKUP(E17,A17:C21,3,FALSE)
You have successfully learnt how to use the Excel lookup and reference functions: HLOOKUP, LOOKUP and VLOOKUP.
