HLookup function (Horizontal Lookup) is commonly used in MS Excel to get values from a data table by matching a value in lookup row and return the value from the matched column. The match type can be define as Exactly match or Approximate match. It works like an English T shape. It matches a value in a row and returned value from another row of same column where value matched.
Function Structure:
The structure of this function is:
There are 4 arguments or parameters in this function. The first 3 arguments or parameters are essential. That means if you want to use HLookup function, then you must mention the first 3 parameters. And the last parameter is optional. By default the last parameter is set to True (Approximate match). But for a better result you need to set the 4th parameter. All of these parameters are briefed here:
lookup_value: Need to mention which value to lookup.
table_array: Range of data table. Please note that, the lookup_value row must be in first row of your selected data table.
row_index_num: Mention the row number from a matched column, which row number will return.
[range_lookup]: Match type Approximate or Exact
Example:
I hope the HLookup function is starting to look a bit more familiar to you. Now let's explore a few examples of using HLookup formulas on real data.
Assume that, A1:E5 is a range of a Players Score Card table. And you want to find out the Score based on the Game Type name. In given table you want to know what is the score of Ronaldo?
Solution:
In C9 cell, use the formula below :
HLookup will find the Hockey value within a data table range from A1:E5 and please note that, the first Row must be contained the Lookup_Value. Other wise it will return a #N/A Error. As you see that, Ronaldo is in the Row Number 5 of your selected A1:A5 data range. That is why the third argument 5 will return the corresponding value of Ronaldo and return the value from row number 5 with exact match FALSE where the Hockey name matched on top row. In example Hockey name matched in D column. The HLookup function returns the 5th row value 12 of D column.
Errors:
The common errors while using this function are mentioned as below:
#REF: The reference cell deleted after applying the function.
#N/A: If the lookup_value not found in the data table or using wrong range.
Function Structure:
The structure of this function is:
=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
There are 4 arguments or parameters in this function. The first 3 arguments or parameters are essential. That means if you want to use HLookup function, then you must mention the first 3 parameters. And the last parameter is optional. By default the last parameter is set to True (Approximate match). But for a better result you need to set the 4th parameter. All of these parameters are briefed here:
lookup_value: Need to mention which value to lookup.
table_array: Range of data table. Please note that, the lookup_value row must be in first row of your selected data table.
row_index_num: Mention the row number from a matched column, which row number will return.
[range_lookup]: Match type Approximate or Exact
Example:
I hope the HLookup function is starting to look a bit more familiar to you. Now let's explore a few examples of using HLookup formulas on real data.
Image 1: Sample data table for HLookup()
Assume that, A1:E5 is a range of a Players Score Card table. And you want to find out the Score based on the Game Type name. In given table you want to know what is the score of Ronaldo?
Solution:
In C9 cell, use the formula below :
=HLOOKUP("Hockey",A1:E5,5,FALSE)
HLookup will find the Hockey value within a data table range from A1:E5 and please note that, the first Row must be contained the Lookup_Value. Other wise it will return a #N/A Error. As you see that, Ronaldo is in the Row Number 5 of your selected A1:A5 data range. That is why the third argument 5 will return the corresponding value of Ronaldo and return the value from row number 5 with exact match FALSE where the Hockey name matched on top row. In example Hockey name matched in D column. The HLookup function returns the 5th row value 12 of D column.
Image 2: Hlookup function returned result 12
Errors:
The common errors while using this function are mentioned as below:
#REF: The reference cell deleted after applying the function.
#N/A: If the lookup_value not found in the data table or using wrong range.