Everything You Need to Know About the HLOOKUP Function In Excel

The HLOOKUP function is used to search for a value in the top row of an array of values, and then it retrieves the value in the same column from a row you specify in the table or array. The H in HLOOKUP stands for ‘Horizontal‘. In this article, we will help you understand how to use the HLOOKUP function along with the example.

This article will explore and discuss the key topics below: 

  • What is the HLOOKUP Function in Excel?
  • Difference between VLOOKUP and HLOOKUP?
  • How to use HLOOKUP in Excel - Example
  • Reasons why HLOOKUP might not be working

What Is HLOOKUP Function in Excel?

HLOOKUP is an Excel function to lookup and which retrieves data from a specific row in a table. It searches for a value in the table's first row and returns another value in the same column from a row according to the given condition.

The HLOOKUP function is available in all versions of Microsoft Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Difference Between VLOOKUP and HLOOKUP?

The VLOOKUP function is used to look up a particular value in a table array organized vertically and then return data from a specified column against that value.

Whereas, we use the HLOOKUP to lookup a value horizontally. This is done by searching the top row of the table for a specified value and in return giving back the value in the same column.

How to Use HLOOKUP in Excel - Example

The HLOOKUP function looks up a value in a given range's first row and reverts it with a value in the same column from the row that was specified. 

Syntax: =HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Consider the master table below.

master-inventory-list.

We will get the warehouse 3 inventory details from the master table where the product code is AX25.

ex1

To use the HLOOKUP function, follow the steps mentioned below.

Step 1. Click the cell where you want the HLOOKUP formula to be calculated.

Step 2. Click formula at the top of the screen, and in the Lookup and reference, select HLOOKUP.

/ex2

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

Step 3. Select the HLOOKUP option. The argument window will appear.

ex3

Step 4. Click OK.

ex4

Key Terms

  • Lookup - Looking for a specified value in the data table.
  • Lookup Value - A value to be searched for.
  • Return Value - A value that is present at the same position but present in another row or column - depending on whether horizontal or vertical lookup is being performed.
  • Master Table - The table from where you will get the matching value.

You can also use the HLOOKUP function using the formula bar. Go to the formula bar and enter =HLOOKUP.

formula1

Enter the above arguments and hit enter. You will get the same result.

Reasons Why HLOOKUP Might Not be Working

The Excel HLOOKUP formula comes with several specificities. If your HLOOKUP is not working, it may be because of one of the following reasons:

  1. There might be some extra spaces in your formula. If you have doubt, use the TRIM function to remove extra spaces.
  2. The lookup value might have exceeded the 255 characters.
  3. You do not have a Lookup_Value in the first row.
  4. You forgot about absolute reference while entering the formula.
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

With this, we reach the close of this tutorial article on HLOOKUP in Excel. We have seen and worked out some examples of HLOOKUP to understand the working of the function. 

If you are looking for an effective way to boost your analytics career with powerful new Microsoft Excel skills, Simplilearn's Business Analytics with Excel course (that includes Power BI training) would be ideal for you.

This Business Analytics certification course helps you get a sound understanding of the fundamental concepts of data analysis and statistics that can be used to help data-driven decision making. This training provides you a great introduction to Power BI and explores in-depth, the statistical concepts that can be used to reveal insights from the data available and use them to present the findings from the data through elegant, easy-to-comprehend and intuitive executive-level dashboards.

Do you have any questions for us? Let us know by sharing them with us in the comments section below. Our team of subject matter experts will review them and promptly answer them for you! 

About the Author

Kartik MenonKartik Menon

Kartik is an experienced content strategist and an accomplished technology marketing specialist passionate about designing engaging user experiences with integrated marketing and communication solutions.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.