Many of the Excel users are known to have skills if they are able to use VLOOKUP formula.
Alternatively there is a little complex formula called HLOOKUP where H stands for “Horizontal” and hence it is often called as Horizontal Lookup. It is a little confusion to most of the users how to use it. The basic task of HLOOKUP function is to search for a value in the topmost row of a table, and then return a corresponding value in the same column from a row you specify.
Lets take a look at the syntax of HLOOKUP function in Excel is as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
Here, ‘lookup_value’ refers to a value that is to be searched in the topmost row of the table. ‘lookup_value’ can be a value, a reference or a text string.
‘table_array’ is the range reference or range name of an array of values, inside which in which the data is to be looked up.
‘row_index_num’ is the row number in the ‘table_array’, from which the matching value is to be returned. A ‘row_index_num’ equal to 1 returns a value from the topmost row in the ‘table_array’ and similarly a ‘row_index_num’ equal to 2 returns a value from the second row of the ‘table_array’.
‘range_lookup’ argument accepts a Boolean value that specifies whether you want Horizontal Lookup function to return an exact match or an approximate match. TRUE stands for approximate match while FALSE stands for an exact match.
Now, let’s understand how to use this Function in Excel.
Consider, we have a Table of Marks obtained by students as shown below:
Objective: In this case, lets say our objective is to fetch Prem's marks in Computers using Horizontal Lookup. So, we will try to apply an HLOOKUP to get the result.
‘lookup_value’: As we have to find the marks of Prem, so our ‘lookup_value’ will be a “Prem”.Alternatively there is a little complex formula called HLOOKUP where H stands for “Horizontal” and hence it is often called as Horizontal Lookup. It is a little confusion to most of the users how to use it. The basic task of HLOOKUP function is to search for a value in the topmost row of a table, and then return a corresponding value in the same column from a row you specify.
Lets take a look at the syntax of HLOOKUP function in Excel is as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
Here, ‘lookup_value’ refers to a value that is to be searched in the topmost row of the table. ‘lookup_value’ can be a value, a reference or a text string.
‘table_array’ is the range reference or range name of an array of values, inside which in which the data is to be looked up.
‘row_index_num’ is the row number in the ‘table_array’, from which the matching value is to be returned. A ‘row_index_num’ equal to 1 returns a value from the topmost row in the ‘table_array’ and similarly a ‘row_index_num’ equal to 2 returns a value from the second row of the ‘table_array’.
‘range_lookup’ argument accepts a Boolean value that specifies whether you want Horizontal Lookup function to return an exact match or an approximate match. TRUE stands for approximate match while FALSE stands for an exact match.
Now, let’s understand how to use this Function in Excel.
Consider, we have a Table of Marks obtained by students as shown below:
Objective: In this case, lets say our objective is to fetch Prem's marks in Computers using Horizontal Lookup. So, we will try to apply an HLOOKUP to get the result.
‘table_array’: In this argument we give the reference of our table i.e. A1:E4.
‘row_index_num’: The ‘row_index_num’ in this case would be 4 as here we have to fetch a value from the fourth row of the table.
‘range_lookup’: ‘range_lookup’ will be FALSE as here we only want to fetch the exact match value.
The result of this formula is 77.
Horizontal Lookup performs a case insensitive lookup,so it doesnt matter if you search the term in upper case or lower case. While using HLOOKUP function ‘lookup_value’ should always be in the topmost row of the ‘table_array’.‘range_lookup’ is an optional argument. If it is omitted then HLOOKUP takes its default value as TRUE (approximate match). Similar to VLOOKUP, HLOOKUP also supports wildcard characters (like: ‘*’, ‘?’) in the ‘lookup_value’ argument (only if ‘lookup_value’ is text).
Comment or get back to me in case you have any queries.
