EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel HLOOKUP in Excel
Secondary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

HLOOKUP in Excel

By Jeevan A YJeevan A Y

HLOOKUP Function in Excel

Excel HLOOKUP Function (Table of contents)

  • HLOOKUP in Excel
  • HLOOKUP Formula in Excel
  • How to Use HLOOKUP in Excel?

HLOOKUP in Excel

When you are in an interview, which demands excel knowledge, it is no surprise that first up, they will ask about what is HLOOKUP & VLOOKUP. It is almost an inevitable skill to have, whether you like it or not.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

When you are working with a small amount of data, it is easy to find anything in the data. However, once the data is getting increased, it will be a very difficult and time-consuming task for you to look for anything within the data.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

That where lookup functions will come into the picture. Excel is a bundle of many formulas that are life to save for many people who work on a large amount of data. Lookup functions will save you valuable time and make you a rock star in the office. In this tutorial, let us go ahead and discuss this in detail.

What is HLOOKUP Function in Excel?

Usually, in excel, we use the terms ROWS & COLUMNS. Have you ever think of an alternative name for these two? Think… Think…

Can we call COLUMNS as VERTICLE and ROWS as HORIZONTAL? The answer is YES!

I told you about this because today in this tutorial, we are going to discuss HLOOKUP, i.e. called “HORIZONTAL LOOKUP”.

If you are a regular user of Excel, you must have come across a function called VLOOKUP, but more often than not, HLOOKUP is under-utilized in most of the offices.

HLOOKUP is used to look through the data horizontally and get the desired result based on the rows to number, i.e. ROW-INDEX number. As the name itself suggests, it will search the data horizontally, whereas VLOOKUP searches the data vertically.

HLOOKUP Formula in Excel

HLOOKUP is a lookup function in excel, and the HLOOKUP formula is as follows.

HLOOKUP formula in Excel 1

  • Lookup_Value: Base Value or Criterion Value to search in the table. It is a reference value to the table.
  • Table_Array: Table in which your targeted values reside. It is raw data to search for the thing you want from the table.
  • Row_Index_Num: It represents the row number where your targeted value is there. The first row is 1.
  • [Range_Lookup]: It consists of two parameters one is TRUE (1) which looks for an approximate match from the table, and another one is FALSE (0), which looks for an exact match from the table.

HLOOKUP formula in Excel 2

Note: TRUE represented by number 1.FALSE represented by number 0.

How to Use the HLOOKUP Function in Excel?

The HLOOKUP function in excel is very simple easy to use. Let us now see how to use HLOOKUP with the help of some examples.

You can download this HLOOKUP Formula Excel Template here – HLOOKUP Formula Excel Template

Example #1

Assume you are working in an HR department of the company and you have the below data (call as master data) in one sheet and another table in another sheet. Now your manager asks you to get their salary details for that master data. Now you must be wondering how I get the desired result out of it. This function can save your life in these cases.

Below is the master data.

Master data

From the above table, you need to get the below employees salary details.

Employees Salary

When the data is small, you can just look into the data and enter the salary details manually. However, in the case of a large amount of data, it does not work like that. So use your life-saving friend HLOOKUP.

Step 1: Select Lookup_Value. Lookup_Value should be unique. There should not be any duplicate values. In the above example, Emp Name has duplicate values so search based on Emp ID.

HLOOKUP Formula in Excel 1

Step 2: TABLE_ARRAY is your actual or raw data, in this example, Master Data. Once you selected the table, you need to lock it by pressing F4 (absolute reference).

Before locking the table range.

Before locking table range

After locking the table range.

After locking table range

Step 3: Row_Index_Num is from which row you are looking for the data. In this case, I am looking for salary details, which is the 3rd row. If I am looking for Emp Name, then it will be row number 2nd.

Emp Name

Step 4: [Range_Lookup] is TRUE or FALSE. If you are looking for an approximate match, then type 1 and if you are looking for an exact match, use 0. Since I am looking for an exact match, I have mentioned 0.

[Range_Lookup]

Result: Now, you will get the result and copy-paste the formula to other cells as well.

Result For Example 1

Example #2 – HLOOKUP with MATCH Function

In the last example, we have seen a detailed explanation of the formula. One major change we can do is we can dynamically get the row_index number.

What Match function does?

The match function can extract methe row number. So I need not enter the row_index number manually.

In Table1, we have a list of our products. In Table2, we need to find their row number.

Table 1

HLOOKUP Example 2

Table 2

HLOOKUP Example 2.1

Table 2: Solution

HLOOKUP Example 2.2

HLOOKUP Example 2.3

Example #3

Table 1: This table represents Product-wise details.

HLOOKUP Example 3

Table 2: From the above table, find out the below details.

HLOOKUP Example 3.1

If you look at the table, all the orders in the rows are changed. By using the Match function, we can get the row_index number.

Match function

The result will be like the below one.

HLOOKUP Function Example 3.3

Things to Remember

  • HLOOKUP can give you an error of #N/A if the Lookup_Value is not matching with the table. In the below picture, Product-6 does not exist in the raw data table.

HLOOKUP Function Example 3.4

  • If the duplicate value is found, then this function will return the first lookup_values result to the remaining lookup_values.
  • You will get an error of #VALUE if Row_Index_Num is less than one.
  • If the Row_Index_Num is greater than the number of rows function will give an error type of #REF.
  • Use HLOOKUP if the data is in horizontal type and if the data is in vertical format, use VLOOKUP.

HLOOKUP Example data structure vs vlookup

Recommended Articles

This has been a guide to HLOOKUP. Here we discuss the HLOOKUP Formula and how to use the HLOOKUP function along with practical examples and downloadable excel templates. You may also look at these useful functions in excel-

  1. LOOKUP in Excel
  2. VLOOKUP Examples in Excel
  3. IF VLOOKUP Formula in Excel
  4. Excel Lookup Function
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download HLOOKUP Formula Excel Template

EDUCBA

Download HLOOKUP Formula Excel Template

EDUCBA

डाउनलोड HLOOKUP Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more