What Are HLOOKUP Examples In Excel?
HLOOKUP, within the realm of Excel functions, is a powerful tool for searching specific values within a horizontal row and returning corresponding data from a different row. It proves particularly useful when working with large data sets where quick retrieval is crucial.
One common example in finance could be using HLOOKUP to fetch sales figures based on the product name entered in a separate cell. Similarly, in project management, it could be employed to extract task completion dates by matching the task ID with the relevant dataset. This function requires four key parameters: the lookup value (the value to search), the table array (the range of cells to scan), the row index number (the row from which to retrieve data), and finally, range_lookup (a logical value determining if an exact match or approximate match is required). For example, there are electronic items displayed below; we need to utilize the HLOOKUP function to obtain the quantity of TV.

Open the HLOOKUP formula and select B1 as the lookup value. Choose the table array, which refers to the main table, as A1:B3. Specify the row number from which we want to retrieve the data from the main table. In this case, the required column is located in row 2. Since we are searching for an exact match, select FALSE or zero (0). The formula to use is =HLOOKUP(B1, A1:B3, 2, FALSE).
By applying these steps, the result will be obtained in cell E3, displaying the quantity of TV as 100.

Table of contents
Key Takeaways
- The HLOOKUP function in Excel is an important function used to retrieve data from horizontal ranges.
- The formula of HLOOKUP is =HLOOKUP(lookup_value,table_Array,row_index_num,[range_lookup])
- Similar to VLOOKUP, HLOOKUP has the limitation of retrieving data from top to bottom rather than from bottom to top.
- The MATCH function provides the row number of the supplied values.
- INDEX + MATCH can be an alternative to the HLOOKUP function in Excel.
- If the row index number falls outside the specified range, the formula will return #REF.
The Formula Of HLOOKUP Function In Excel
The syntax of the HLOOKUP function in Excel is displayed in the image below. A comprehensive explanation of the arguments accepted by the function is presented following the image.

- lookup_value: This refers to the value being searched for in the topmost row of the table.
- table_array: This represents the reference or name of the table array from which the desired data is retrieved. It is important to note that the lookup value must be located in the topmost row of the table.
- row_index_number: This denotes the row number within the table array where the value is matched and returned. For instance, if the row_index_number is set to 1, the function will return the value from the topmost row of the table_array. Similarly, if the row_index_number is set to 2, the function will retrieve the value from the second row of the table_array, and so forth.
- range_lookup: This parameter determines whether the function performs an approximate or exact match. When range_lookup is set to TRUE (equivalent to 1), the function conducts an approximate match. Conversely, the function performs an exact match when range_lookup is set to FALSE (equivalent to 0). The function will return the #N/A error if neither condition is met.
HLOOKUP Examples In Excel
Example #1
Assume the marks of two students in four subjects are displayed below. We need to get marks of Dreco in English using the HLOOKUP function.

Step 1: To begin, open the HLOOKUP formula and select the lookup value as C1.
Step 2: Next, we must select the table array, which refers to the main table as A1:C5.
Step 3: We also need to specify the row number from which we want to retrieve the data from the main table. In this example, the required column is located in row 4.
Step 4: Lastly, we need to determine the range lookup. Since we are looking for an exact match, we should select the option “FALSE” or “zero” (0).
Step 5: The formula is =HLOOKUP(C1, A1:C5,4, FALSE).

Step 6: The result is obtained in cell B7 as 77.

Example #2 – HLOOKUP + MATCH Formula
To effectively analyze the computer peripherals market, utilizing the HLOOKUP function in conjunction with the MATCH function to obtain accurate results is crucial.
To initiate this process, please follow the following steps:

Step 1: Open the HLOOKUP formula and designate A9 as the lookup value.
Step 2: Select the table array, which refers to the main table, as B1:F5.
Step 3: Specify the lookup value for the Match function as B8.
Step 4: Choose the lookup array corresponding to the table as A1:A5.
Step 5: Determine the match type as 0.
Step 6: Establish the range lookup. Since we seek an exact match, select FALSE or zero (0).
Step 7: The formula to be employed is
=HLOOKUP($A9,$B$1:$F$5,MATCH(B$8,$A$1:$A$5,0),0).

Step 8: By diligently following these steps, the desired outcome will be obtained and displayed in columns B9:E13, as illustrated below.

Example #3 – INDEX + MATCH As The Alternative To HLOOKUP
We can utilize the MATCH + INDEX function as an alternative to obtain the desired result rather than relying on the HLOOKUP function.

To achieve this, follow these steps:
Step 1: Select the table array, which refers to the Index function, as B2:E2.
Step 2: Specify the lookup value for the Match function as B4.
Step 3: Choose the lookup array corresponding to the table as B1:E1.
Step 4: Determine the match type as 0.
Step 5: The formula used is =INDEX(B2:E2,MATCH(B4,B1:E1,0)).

Step 6: We will achieve the desired outcome in cell B5 by diligently following these steps.

Important Things To Note
- The versatility and efficiency of the HLOOKUP provide professionals with invaluable assistance in data organization and analysis within Excel workbooks.
- An error of #N/A may occur if the lookup_value does not exactly match any value in the data table.
- The structure of the data table is of utmost importance. If the table is arranged horizontally, the HLOOKUP function should be utilized. Conversely, the VLOOKUP function is more appropriate if the table is arranged vertically.
Frequently Asked Questions (FAQs)
• HLOOKUP can only search for values in the first row of a range. This means it cannot search for values in subsequent rows, limiting its functionality when working with multi-row datasets.
• Additionally, HLOOKUP will only return the first match it finds. If multiple matches are within the specified range, HLOOKUP will not retrieve them all but instead provide only the first occurrence.
• One of the common errors is specifying an incorrect range for the lookup table. Selecting the correct range of cells when defining the lookup table to retrieve accurate results is essential.
• Another common mistake is failing to specify the exact match or approximate match parameter within the formula. If this parameter is neglected or entered incorrectly, HLOOKUP may return incorrect values or fail to find matches.
To illustrate, let’s consider the items listed below. To obtain the value of these items, we can effectively utilize the HLOOKUP function.
First, open the HLOOKUP formula and designate B1 as the lookup value. Next, select the table array, which refers to the main table as A1:B3. Specify the row number from which we wish to retrieve the data from the main table. In this particular case, the required column is located in row 3.
Now, let’s determine the range lookup. Since we are searching for an exact match, we should select FALSE or zero (0). The formula used is =HLOOKUP(B1, A1:B3, 3, FALSE).
By following these steps, the desired result will be obtained in cell E2, displaying the value of the items as 4.
Download Template
This article must help us understand the HLOOKUP Examples in Excel formulas and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to HLOOKUP Examples In Excel. Here we learn the formula of HLOOKUP function in excel with step by step examples and template. You can learn more from the following articles –
Leave a Reply