EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Lookup & Reference Functions in Excel HLOOKUP in Excel

HLOOKUP in Excel

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated October 18, 2023

What is HLOOKUP in Excel?

The HLOOKUP function in Excel performs a horizontal search across a sorted data table to find a match with the lookup value. It searches across the topmost row for the look-up value and then moves down to retrieve data from the specified row.

In HLOOKUP, the H stands for Horizontal, which we use when the lookup table is arranged horizontally. We can use the function to search for numbers, text, or logical values.

For example, =HLOOKUP(A11, B5:F6,4,0) scans the lookup table to find the price of OPPO and gives the result of $3,435.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

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

HLOOKUP in Excel

Key Highlights

  • In the HLOOKUP in Excel, “H” stands for horizontal. The HLOOKUP function scans from the top and moves to the last row of the data table.
  • HLOOKUP scans the data table for exact and approximate matches of the looked-up value.
  • If HLOOKUP in Excel finds duplication in the data range, the function returns only the first match.
  • TRUE in the HLOOKUP argument indicates an absolute match, and FALSE indicates a close match.

Syntax of HLOOKUP in Excel

The syntax for the HLOOKUP function is-

HLOOKUP syntax

  • Lookup_Value: It is the Base Value or Criterion Value to search in the table.
  • Table_Array: It is the table range in which the targeted value resides.
  • Row_Index_Num: It represents the row number where the targeted value is present. The first row is entered as 1, the second as 2, and so on.
  • [Range_Lookup]: It consists of two parameters- TRUE or 1 searches for an approximate match in the data table; FALSE or 0 looks for an exact match.
Note: For Approximate match, the values in the top row of the data table must be arranged in ascending order.

How to Use HLOOKUP in Excel?

To understand the use of HLOOKUP, consider the following examples:

You can download this Hlookup in Excel Template here – Hlookup in Excel Template

Example #1

The table below shows students’ marks in Grade 12 in the subjects- Chemistry, Maths, Biology, and English. We want to find the marks scored Emma in Maths using the HLOOKUP function.

HLOOKUP in Excel-example 1 question

Solution:

Step 1: Place the cursor in cell B16 and type the formula,

=HLOOKUP(B15,A5:G12,5,0)

example 1 solution

Explanation:

  • B15: It is the look-up value (Maths)
  • A15:G12: It is the cell range from which HLOOKUP will search the marks
  • 5: It indicates the row number from which Excel needs to fetch data
  • 0: It indicates that we want the Exact match

Note: We can use FALSE or 0 to indicate an Exact match.

Step 2: Press Enter key to get the below result

step 2

The HLOOKUP function displays that Emma scored 89 marks in Maths.

Example #2

The table below shows master data consisting of Employee ID, Employee Name, and Salary. We want to find the salary of all the employees using HLOOKUP in Excel.

example 2

Solution:

Step 1: Place the cursor in cell B13 and enter the formula,

=HLOOKUP(B11,$B$6:$F$8,3,FALSE)

eg 2 step 1

Note: We want to find lookup values from the same table range. Therefore, we have locked it using the $ symbol, i.e., $B$6:$F$8.
Shortcut: To lock or freeze a data range, press the F4 key of the keyboard.

Step 2: Press Enter key to get the below result

eg 2 step 2

The HLOOKUP function gives a salary of $50,047 against Emp ID 128.

Step 3: Drag the formula in other cells to get the below result

HLOOKUP in Excel-eg 2 step 3

Using HLOOKUP and MATCH Function Together

We use the Match function to look up multiple values from a data table. Whenever we change the look-up value, the Match function fetches the corresponding row number for HLOOKUP to give accurate results. In other words, we use the Match function when the row number is dynamic.

Consider the below example to understand the working of the Match function

Example #1

The below table shows the sales figures of 5 salespeople for Jan, Feb, Mar, Apr, May, and Jun. We want to find the row number for the month “May” using the MATCH function.

example

Solution:

Step 1: Place the cursor in cell B15 and enter the formula,

=MATCH(A15,A5:A11,0)

example step 1

Step 2: Press Enter key to get the below result

HLOOKUP in Excel-example step 2

The MATCH function searches for A15 (May) in the column range A5:A11 and provides its row number of 6.

Example #2

The below table shows the sales figures of 5 salespeople for Jan, Feb, Mar, Apr, May, and Jun. We want to find the sales figure of James in May using the HLOOKUP and MATCH functions.

example 3

Solution:

Step 1: Place the cursor in cell D16 and enter the formula,

=HLOOKUP(C16,C7:H13,MATCH(D15,C7:C13,0),0)

example 3 step 1

Explanation: The HLOOKUP searches for C16 (James) in the range C7:H13

The MATCH function searches for D15 (May) only in the C column in the data range C7:C13 to give the row number 6. Therefore, HLOOKUP fetches the sales figure of James from row 6.

Step 2: Press Enter to get the below result,

HLOOKUP in Excel-eg 3 step 2

The HLOOKUP and Match function provides the sales figure of 11400.

HLOOKUP in Excel Errors

The HLOOKUP function displays the following Errors:

  1. #N/A!: Excel gives this error when the HLOOKUP function cannot find the lookup_value in the table_array.

For example,

Cell B17 (lookup value) in the table below contains no data. Therefore, HLOOKUP displays the error #N/A!

HLOOKUP in Excel Errors

  1. #REF!: This error occurs when we provide a row number (row_index_ num) greater than the number of rows in the data table (table_array).

For example,

In the table below, the row number (9) we have provided in the formula is greater than the table’s number of rows. Therefore, HLOOKUP displays the error #REF!

HLOOKUP in Excel Errors 2

  1. #VALUE!: Excel gives this error when we do not provide the row number (row_index_ num), or we have given a value less than 1.

For example,

In the below table, we have not provided the row number in the formula. Therefore, HLOOKUP displays the error #VALUE!

HLOOKUP in Excel Errors 3

Frequently Asked Questions (FAQs)

Q1) Is HLOOKUP better than VLOOKUP?

Answer: VLOOKUP and HLOOKUP are useful when finding data in a large database with many rows and columns. However, both HLOOKUP and VLOOKUP have advantages and disadvantages.

We can use Vlookup only when the lookup value is in the first column and HLOOKUP when the look-up value is in the first row of the data table. The functions fail to give accurate results when the data set has duplicate values.

Q2) What is the syntax of HLOOKUP?

Answer: The syntax of HLOOKUP is:

=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

  • Lookup_value: The value you want to find in the 1st row of the cell range.
  • Table_array: It is the table that contains lookup data. It is important to ensure the lookup data is in the top row of the table array.
  • row_index_num: It is the row containing the desired value.
  • [range_lookup]: It requires FALSE or 0 to provide an exact match to the look-up value and TRUE or 1 for an approximate match.

Q3) How do I use HLOOKUP in sheets?

Answer: To use HLOOKUP in Google Sheets, consider the below example to find the number of apples sold on Day 4.

Step 1: Select the cell where you want the result

Step 2: Place the cursor and type the formula

=HLOOKUP(“Apple”,B1:E6,5,0)

Step 3: Press Enter key to get the result of 16

faq 3

The HLOOKUP searches for the lookup_value (Apple) in the table range B1:E6, moves horizontally to row number 5 and provides the value of 16.

Or,

We can also locate the HLOOKUP function in Google Sheets using the Insert menu,

Insert > Function > Lookup > HLOOKUP.

faq

  1. LOOKUP in Excel
  2. VLOOKUP Examples in Excel
  3. IF VLOOKUP Formula in Excel
  4. Excel Lookup Function
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

EDUCBA

Download Hlookup in Excel Template

Let’s Get Started

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

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

EDUCBA

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

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

Forgot Password?

EDUCBA

Download Hlookup in Excel Template

EDUCBA

डाउनलोड Hlookup in 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

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW