HLOOKUP Function and Its Uses

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 10% off till Sep 11, 2017
- Batch starts from October 8, 2017

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 

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "HLOOKUP Function and Its Uses"

Post a Comment

Next → ← Prev