Explanation
The Hlookup function looks for a value in the top row of a table and then returns a value in the same column from a row you specify.
HLOOKUP vs VLOOKUP
Syntax
The syntax for Hlookup function is as follows :
In Layman's Terms
Detailed Explanation
lookup_value - It is the value to be found in the top row of the table.
table_array - It is a table of text, numbers or logical values. Table_array can be a cell reference or a range name.
row_index_num - It is the row number in table_array from which the matching value should be returned.
range_lookup -
1 (TRUE) - Find an approximate match
0 (FALSE) - Find an exact match
Note : If you don't enter a value in the fourth parameter "range_lookup" of hlookup, excel will consider 1 as a logical value i.e. closest match.
Example
Suppose you have data for students' names and their classes. You want to find the section names for students in data table.
Download the workbook used in the examples shown below and practice what you learn.
The data is shown in the image below :
In cell E3, enter the following formula and paste down till cell E8:
Limitation of HLOOKUP
Download the workbook
The Hlookup function looks for a value in the top row of a table and then returns a value in the same column from a row you specify.
HLOOKUP vs VLOOKUP
HLOOKUP is just like the VLOOKUP formula. It's just the rows and columns being switched.
HLOOKUP - Horizontal Lookup
VLOOKUP - Vertical Lookup
Syntax
The syntax for Hlookup function is as follows :
HLOOKUP (lookup_value, table_array, row_index_num ,range_lookup)
In Layman's Terms
HLOOKUP (value to be found, table reference, pull a matching value from the Kth row of the table , exact match / closest match)
Detailed Explanation
lookup_value - It is the value to be found in the top row of the table.
table_array - It is a table of text, numbers or logical values. Table_array can be a cell reference or a range name.
row_index_num - It is the row number in table_array from which the matching value should be returned.
range_lookup -
1 (TRUE) - Find an approximate match
0 (FALSE) - Find an exact match
Note : If you don't enter a value in the fourth parameter "range_lookup" of hlookup, excel will consider 1 as a logical value i.e. closest match.
Example
Suppose you have data for students' names and their classes. You want to find the section names for students in data table.
Download the workbook used in the examples shown below and practice what you learn.
The data is shown in the image below :
=HLOOKUP(B3,$B$11:$G$12,2,0)
Explanation
Limitation of HLOOKUP
To make HLOOKUP work, you need to have a lookup value in the first row of the table. If your look up value is not placed in the first row of the table, it will return #N/A error.
Look up value must be in the top row of a table
Download the workbook
Share Share Tweet