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.
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.
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.
We will get the warehouse 3 inventory details from the master table where the product code is AX25.
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.
Step 3. Select the HLOOKUP option. The argument window will appear.
Step 4. Click OK.
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.
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:
- There might be some extra spaces in your formula. If you have doubt, use the TRIM function to remove extra spaces.
- The lookup value might have exceeded the 255 characters.
- You do not have a Lookup_Value in the first row.
- You forgot about absolute reference while entering the formula.
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 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!
