WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » HLOOKUP in Excel

HLOOKUP in Excel

Hlookup in Excel

Hlookup in excel is very similar to Vlookup in excel, Hlookup is a referencing worksheet function in excel which uses a reference and finds and matches the value from a row instead of a column, that is why the name was given to this formula as Hlookup which means horizontal lookup where we search for data horizontally in rows.

Formula

Hlookup Formula in excel

Explanation

There are four parameters used in the HLOOKUP function in which three are compulsory, and one is optional.

Compulsory Parameter:

  • Lookup_value: lookup_value represents the value which you want to search in table array; it can be a valuable reference and a string passed in the Hlookup formula.
  • Table_array: table_array is the raw data where you want to search for the lookup value.
  • Row_index_num: Row_index_num is the number of rows in table array from which you need the required details for a particular lookup value.

Optional Parameter:

  • [Range_lookup]: [Range_looup] is an optional parameter where the value will be 0 or 1.

Here 0 stands for exact match value, and 1 stands for approximate match value.

How to use HLOOKUP in excel? (with Examples)

This function is very simple and easy to use. Let’s understand the working of HLOOKUP with examples.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
You can download this HLOOKUP Excel Template here – HLOOKUP Excel Template

Example #1

Consider the below table as a raw table, and we need specific data from the raw table.

HLOOKUP Function example 1

Suppose you need a region-wise data of total sales, profit, and date like the show in the below table.

HLOOKUP Function example 1-1

For Profit: =HLOOKUP($A10,$A$4:$E$7,2,0), output will be 575.

For Sales: =HLOOKUP($A10,$A$4:$E$7,3,0), output will be 120.

For Date: =HLOOKUP($A10,$A$4:$E$7,4,0), output will be 5/30/2018.

Example #2

Let’s pick another Hlookup example here to understand the exact match value and approximate match value in the HLOOKUP function in Excel.

Consider the below data as table_array or raw data.

HLOOKUP Function example 2

And you need to find out the percentage of data 1st Jan and 2nd April.

HLOOKUP Function example 2-1

For 1st April: =HLOOKUP(A20,A15:E16,2,0) or =HLOOKUP(A20,A15:E16,2,False) here false for exact match value.

For 2nd April: =HLOOKUP(A21,A15:E16,2,1) or  =HLOOKUP(A20,A15:E16,2,True) here true is for approximate match value.

Things to Remember

  • HLOOKUP function in excel is a case-insensitive lookup. It means if you write tanuj and Tanuj, it will treat it the same with no effect on the output due to letter case.
  • There is a limitation in HLOOKUP is that the ‘Lookup_value’ should be the topmost row of the ‘table_array.’ If you need to retrieve the data from something else row, then other excel formula is used in place of HLOOKUP in excel.
  • HLOOKUP supports special characters such as “*” or “?” in the ‘lookup_value’ parameter only if ‘lookup_value’ is a text string.

Error in HLOOKUP Function

Three kinds of error can come in the HLOOKUP function in excel due to wrong arguments. 

  1. #N/A error: HLOOKUP returns the #N/A error if ‘range_lookup’ is FALSE/0 sets to exact match, and the HLOOKUP function in excel is unable to find the ‘lookup_value’ in the given table_array.

example (NA Error)

  1. #Value error: If the supplied ‘row_index_num’ < 1 and < the number of columns in table_array, then HLOOKUP would return VALUE! error.

Ex.- =HLOOKUP(A20,A15:E16,0,0)

example (VALUE Error)

  1. #REF! error:If the supplied ‘row_index_num’ < the number of columns in table_array, then Hlookup in excel would return #REF! error.

Ex.- =HLOOKUP(A20,A15:E16,3,0)

example (REF Error)

  1. The HLOOKUP function can only retrieve the first value matched value in the table_array that matches the lookup value.

If there are a few duplicate records in the table_array, then it will return the first match value; to avoid this situation, you can remove the duplicates values from table_array then apply HLOOKUP in Excel.

If duplicate records should be kept in the dataset, create a PivotTable to group your data the way you want, or you may use an array formula to extract all duplicate values in the lookup range.

HLOOKUP Function Video

Recommended Articles

This has been a guide to HLOOKUP in Excel. Here we discuss its 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

  • VLOOKUP with Two Criteria
  • HLOOKUP Examples in Excel
  • VLOOKUP Errors in Excel
  • Power BI Vlookup
  • Price Function in Excel
1 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 >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. 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

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download HLOOKUP Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More