• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

HLOOKUP Examples

Home » Excel » Lookup & Reference Functions in Excel » HLOOKUP Examples

By Sharmila Reddy Leave a Comment

HLOOKUP Examples

HLOOKUP Examples in Excel

In this article, we will take examples of HLOOKUP function in excel. Before I give you examples of HLOOKUP function let me introduce you to the HLOOKUP function first.

The Formula of HLOOKUP FUNCTION in Excel

The Formula of the HLOOKUP function includes 4 arguments. All the parameters are the same as the VLOOKUP function.

HLOOKUP formula

  1. Lookup_Value: This is the value we are considering it as a base value to find the required result.
  2. Table_Array: This is the data table which has lookup value as well as result value.
  3. Row_Index_Num: This is nothing but in which row our result is there in the data table.
  4. [Range_Lookup]: Here we have two parameters first one is TRUE (1) which finds an approximate match from the table and the second one is FALSE (0) which finds the exact match from the table.

HLOOKUP formula Range

  • TRUE parameter can be passed as number 1.
  • FALSE parameter can be passed as number 0.

HLOOKUP Examples in Excel

Here are some examples of HLOOKUP Function in Excel.

HLOOKUP Example #1

Assume you are working in an HR department and you are dealing with employee’s information like salary, DOJ,… etc. For example, look at the below data.

HLOOKUP Example 1

This is the master data you have. From the finance team, you have received the Emp ID and they have requested for their salary information to process the salary for the current month.

HLOOKUP Example 1-1

Now don’t get confused with the data structure here, because in the main the data, data is there in horizontal form but the request came in vertical form.

If you are confused which formula to apply, the first thing you need to look for is what the data structure of the main data table is. It doesn’t matter if the required table is in vertical form or horizontal form. Only thing matters is how is the main data table.

Since our main table is in horizontal table lets apply HLOOKUP to fetch the data.

Step 1: Open the HLOOKUP formula in the salary column and select the lookup value as Emp ID.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

HLOOKUP Example 1-2

Step 2: Next thing is we need to select the table array i.e. main table.

HLOOKUP Example 1-3

I have locked the main table range by pressing the F4 key. This becomes an absolute reference now.

Step 3: Now we need to mention the row number i.e. from which row of the main table we are looking for the data. In this example, a row number of the required column is 4.

HLOOKUP Example 1-4

Step 4: The final part is a range lookup. Since we are looking at the exact match we need to select the option as FALSE or zero (0).

HLOOKUP Example 1-5

We are done we got the value we required through HLOOKUP function.

HLOOKUP Example 1-6

Drag the Formula to get the result to rest of cells.

HLOOKUP Example 1-7

Example #2 – HLOOKUP + MATCH Formula

I will take the same data for example but here I have added department against each employee name.

HLOOKUP Example 2

I have another table which requires all the information above information based on the Emp ID but all the data columns are not in order.

HLOOKUP Example 2-1

If we manually supply row number we need to keep editing the formula for all the columns. Instead, we can use the formula MATCH which can return the row number based on the column heading.

HLOOKUP Example 2-2

In the row index number apply MATCH function and get the row numbers automatically. Apply the formula as shown in the below image.

HLOOKUP Example 2-3

Mention the final argument and close the formula.

HLOOKUP Example 2-4

We got the result.

HE Output 2-5

Drag the formula to other cells we will have results.

HE Output 2-6

One problem here is we don’t get the format for the date column. We need to manually apply the date format.

HE Output 2-7

Apply the above format to date column we will have correct date values now.

HE Output 2-8

Example #3 – INDEX + MATCH as the Alternative to HLOOKUP

We can apply MATCH + INDEX function as the alternative to get the result instead of HLOOKUP function. Look at the below screenshot of the formula.

HE Output 3

The output is given below:

HE Output 3-1

Things to Remember about HLOOKUP Examples

  • We will get an error of #N/A if the Lookup_Value is not the exact value in the data table.
  • Data table structure matters a lot. If the data table is in horizontal form then HLOOKUP should be applied and if the table is in vertical form then VLOOKUP function should be applied.
  • Like VLOOKUP, HLOOKUP too has a limitation of fetching the data from top to bottom not from bottom to top.
  • MATCH function returns the row number of supplied values.
  • INDEX + MATCH can be used as an alternative to HLOOKUP function in excel.
  • If the row index number is not in the range formula would return #REF.

You can download this HLOOKUP Example Excel Template here – HLOOKUP Examples Excel Template

Recommended Articles

This has been a guide to HLOOKUP Examples in Excel. Here we discuss examples of HLOOKUP Function and also the Alternative of HLOOKUP (INDEX + MATCH) in excel with downloadable excel template. You may learn more about excel from the following articles –

  • VLOOKUP in Excel Pivot Table
  • What is MATCH Formula in Excel?
  • P Value Formula
  • How to Use LOOKUP Formula in Excel?
  • Range Formula
  • P-Value in Excel
  • VLOOKUP vs HLOOKUP- Compare and Contrast
  • VLookup function in VBA
  • How to Fix VLOOKUP Errors
  • Using VLOOKUP with Multiple Criteria
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Lookup & Reference Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More