Master Excel's H Lookup: The Ultimate Guide to Finding Data Faster

What is HLOOKUP Function in Excel?

HLOOKUP is an Excel function to look up and which retrieve 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.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Syntax/ HLOOKUP Formula

The syntax of the HLOOKUP or the  HLOOKUP Formula is given as follows:

The attributes of the HLOOKUP function syntax are:

The attributes of the HLOOKUP function syntax are:

1. Lookup_value (Mandatory): The lookup value is in the first row of the Excel table. It can be a reference, a value, or a text string.

2. Table_array (Mandatory): The table array is the information table comprised of several cells in which one looks up the data. Users must provide a range or range name associated with a reference.

• The table_array’s first row can have numbers, text, or logical values.
• In the case of a TRUE range_lookup, the first row values of table_array must be set in ascending order. For example, -1, 0, 1, 2, 3, and so on; A-Z, FALSE, TRUE. If it is not in a set order, HLOOKUP may give an incorrect value. The table_array is not required to be sorted if the range_lookup is FALSE.
• Lowercase and uppercase texts are considered equivalent.
• The sorting of values must be in ascending order, i.e., from left to right.

3. Row_index_num (Mandatory): The row_index_num denotes the specific row number in the table_array where the matched value will be returned. The first row of the table array is returned by a row_index_num of 1; the second row is returned by a row_index_num of 2, and so on. 

• If row_index_num < 1, the HLOOKUP function returns or yields the #VALUE! error value.
• If row_index_num > the number of rows on table_array, the HLOOKUP function returns or yields the #REF! error value.

4. Range_lookup (Optional): The range _lookup is an argument specifying whether one wants HLOOKUP to locate an exact/approximate match. An approximation match will be made if you type "TRUE" or omit a value. A specific match will be used if you enter "FALSE". The error code #N/A is returned if a specific value cannot be found.

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.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

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.

Approximate Match in HLOOKUP

Approximate match in HLOOKUP is a mode to find a value in the first row of a table array allowing one to locate a value that may not be an exact match. It returns the nearest match to the value looked for from the table array.

You can specify whether you want the exact match or an appropriate one by setting the range_lookup argument. You may use an approximate match when searching for data that is not an exact match, but still, a result is required.

Let us use the example to understand the approximate match in the HLOOKUP function. We will use the master table to explain.

Figure 1

Suppose we want to find out whether there are 110 products in any inventory. However, we are still determining the product code. In such a case, the previously stated optional function Range_lookup is required. For instance, we remember that it lies between “XP200” and “XP300”. Finding in such a case would be based on approximation. We will find the approximate value using' true', while ‘false’ will give the exact match. Thus we prefer the former option. Here is a step-wise method:

Step 1: Click on the cell where you want the results to be displayed. 

Step 2: Click on the ‘HLOOKUP’ formula

Step 3: In ‘Lookup_value’, write in the approximate range of products such as “XP250” and FALSE in Range_lookup (Check the dialogue box and formula at the top of  Figure 2. 

Figure 2

Step 4: The result displays “#N/A” which indicates the absence of a match or exact value (Check the HLOOKUP formula on top of Figure 3). 

Figure 3

Let us perform the same steps with a TRUE value in the range_lookup function. Check the HLOOKUP formula and dialogue box in Figure 4 below. 

Figure 4

We see the result “110” displayed in Figure 5. 

Figure 5

Note: Ensure that the values in the first row of the table are in ascending order in case of the approximate match in HLOOKUP to make the function return the expected results.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

HLOOKUP From Another Workbook or Worksheet

Using the HLOOKUP, you can find a value in a table array in another workbook/worksheet other than the one where the HLOOKUP function is used. We already have sheet 1 depicted above. We are creating a similar sheet 2 in Figure 6. 

Figure 6

Here are the steps to perform the same: 

Let us see if we want to export an entry from sheet 2 to sheet 1.

Step 1: Select the cell where you want to export the values. 

Step 2: Go to the HLOOKUP function and enter the credentials of sheet 2 as described previously and depicted in Figure 7. 

Figure 7

Step 3: Next, the result is displayed in the selected column. Figure 8 represents the export of value from sheet 2 to sheet 1.

Figure 8

Step 4: Repeat the steps for every value. (HLOOKUP returns single row value)

Export Multiple Values (Single Row Value From Column)

We can also enlist, let’s say, the product constituents in a single row by exporting multiple values in a single function. Here is how to perform the same: 

Step 1: Use the HLOOKUP function and enter the credentials. Ensure the same number of cells as per the number of export values. 

Step 2: Write the row number to be exported in curly brackets separated with commas and without space, as depicted in Figure 9.

Figure 9

Step 3: Remember to press Ctrl + Shift + Enter rather than simply enter. Else, the return value will only be the first selected row. Combining keys will ensure returning the value in one go by enclosing the HOOKUP formula in curly brackets. 

Step 4: The result in Figure 10 displays the product code with its constituent items.

Figure 10

HLOOKUP from another workbook or worksheet is beneficial in cases where the data is stored in multiple workbooks/worksheets from where you want to search and retrieve data.

Make sure to specify the name of the workbook or worksheet in the function's table_array parameter if you want to use HLOOKUP from a different workbook or worksheet. The syntax for the table_array argument can be used as [Workbook]Worksheet! Range

• Workbook: The workbook name with the table array to be used.
• Worksheet: The worksheet name contains the table array to be used.
• Range: The range of cells containing the table array.

Note: 

• The workbook name must be enclosed in single quotes if it contains spaces.
• To access the data, Excel may prompt you to provide a password or unhide the worksheet if it has been closed or hidden within the workbook.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

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 doubts, use the TRIM function to remove extra spaces.
2. The lookup value might have exceeded 255 characters.
3. You do not have a Lookup_Value in the first row.
4. 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!