HLOOKUP Function and Its Uses

Deepanshu Bhalla Add Comment
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

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 :



In cell E3, enter the following formula and paste down till cell E8:
=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 
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

0 Response to "HLOOKUP Function and Its Uses"
Next → ← Prev