In this blog, we will understand the formula of HLOOKUP in Excel and learn about its usage in Excel with the help of real-life examples. By the end of this blog, you will have a clear understanding of all the concepts related to HLOOKUP in Excel.
Table of Contents
Watch this video to learn more about Excel in detail:
What is HLOOKUP in Excel?
HLOOKUP is a useful tool that stands for horizontal lookup in Excel. It allows users to search for a specific value in the first row of a table or a range and retrieve related information from the row below based on matching criteria. It allows you to horizontally search for data in a table and fetch corresponding information from another row, thus helping in easy retrieval and analysis of data within spreadsheets.
Do you want to know more about data analytics? Enroll in this professional Data Analytics Course and learn from experts.
HLOOKUP has a simple formula for searching the data in the table. Let us see the basic formula for HLOOKUP in Excel.
General HLOOKUP Formula Format:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- Lookup value: The value to be searched for in the first row of the table
- Table_array: The range that contains the data
- Row_index_number: The row number in the table from which the data is retrieved
- Range_lookup: 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.
How Do You Use HLOOKUP in Excel?
To demonstrate the use of HLOOKUP in Excel, we will consider an example of student data where we will take the marks of different students in different subjects, as shown in the table below:
Now, we will use HLOOKUP to find the marks of a student in different subjects. We will write the name, and it will return the output as per the formula and conditions.
In the below example, we look for the marks of Ramesh in math.
Write the below formula in the formula bar of the output cell.
=HLOOKUP(“Ramesh”,A1:G6,4,FALSE)
Alternatively, we can perform the following steps to use HLOOKUP in Excel:
1st Step: Select the cell on which you want to implement HLOOKUP.
2nd Step: Go to the Formulas Tab > Lookup & References > HLOOKUP.
3rd Step: The function Arguments window will appear. Fill in the required fields and press OK.
Output:
In the above example, we have used HLOOKUP for all the subjects of a particular student. If you change the student name, the details of that particular student will appear as an output.
Points to remember while applying HLOOKUP in Excel:
- Lookup: Looking for a specified value in the data table
- Lookup Value: A value to be searched for
- Return Value: A value present at the same position but in another row or column, depending on whether a horizontal or vertical lookup is being performed.
- Master Table: The table from which you will get the matching value.
Enroll in this Excel Course to learn more about it in detail from beginner to advanced level.
Difference Between HLOOKUP and VLOOKUP
HLOOKUP and VLOOKUP functions are used to search for or look for a certain value under specific conditions in a table. HLOOKUP searches horizontally in the row of a table, while VLOOKUP searches for the values vertically in the columns of a table.
Here is a detailed differentiation table for HLOOKUP and VLOOKUP in Excel:
| Parameter | HLOOKUP | VLOOKUP |
| Functionality | Searches horizontally in the row of a table | Searches vertically in the columns of a table |
| Syntax | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) |
| Lookup direction | Search for a value in the topmost row | Look for values in the leftmost column |
| Indexing | Uses the row numbers to identify the results | Uses the column number to identify the result |
| Usage | Extracts data from the horizontal dataset | Retrieving data from a vertical dataset |
How to Use HLOOKUP from Another Worksheet
We can also find a value from another worksheet. It is like referencing from different tables to fetch the desired records in Excel using HLOOKUP.
Here, we have added another subject in another worksheet named sheet2, and we will call it in sheet1 for implementing the HLOOKUP, taking reference from sheet2 by using the formula below.
HLOOKUP formula format for other sheets:
=HLOOKUP(B10,Sheet2!A1:G2,2,FALSE)
Output:
Export Multiple Values (Single Row Value From Column)
We can also enlist the marks of a particular student in a single row by exporting multiple values in a single function. Here are the steps 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 { } brackets separated with commas without space, as shown in the image below.
Step 3: Follow the above steps and see the output of using the HLOOKUP in Excel. It will select all the columns from the referenced cell as per the formula.
Formula used:
=HLOOKUP(“Reena”,A1:G6,{1,2,3,4,5,6},FALSE)
Output:
In the above example, we have taken all the marks in every subject of the column named “Reena,” using the formula for HLOOKUP with certain conditions.
Prepare yourself for the industry by going through these Excel Interview Questions!
Approximate Match in HLOOKUP
In an approximate match, we look for approximate matching values from the dataset. It returns the nearest match to the given argument. Suppose, in the above table, we want to search for ‘Reshma,’ and in the conditions of ranges, we will pass the logical value as ‘TRUE’. The approximate match in HLOOKUP does not look for exact matches; rather, it focuses on the approximate values. If we pass the logical value as ‘FALSE,’ it will look for the exact match in the dataset.
We will demonstrate it with the help of an example, using the below-mentioned dataset. Let us look at the example and understand it in detail.
Steps to find the approximate match in HLOOKUP:
Step 1: Click on the cell where you want to use the approximate match using HLOOKUP in Excel.
Step 2: Go to the formula tab and select HLOOKUP from the drop-down menu.
Step 3: Write all the values and arguments.
Step 4: If there is an exact match, it will show the output; otherwise, it will show “NA” as the output.
Using the above steps, we can find an approximate or exact match in the dataset using Excel’s HOOKUP() function.
Reasons Why HLOOKUP is Not Working
HLOOKUP in Excel does not work for several reasons. Let us discuss some of the reasons in detail:
- Extra Spaces: Check for the extra spaces while writing the formula in the HLOOKUP implementation in Excel.
- Special Characters: Check for the special characters in the formula.
- Text Length: The length of the text should not exceed 255 characters.
We have to take care of the above conditions if the HLOOKUP function does not work in Excel.
Conclusion
In summary, HLOOKUP is a very useful tool in Excel to perform various operations easily. Through this blog, we have learned how to use the HLOOKUP function in Excel, the difference between VLOOKUP and HLOOKUP, and the different operations that we can perform using different functions, such as approximate functions. We learned to select a Single Row Value from a column to fetch the data based on certain HLOOKUP criteria in Excel.
FAQs
What does HLOOKUP stand for in Excel?
HLOOKUP stands for Horizontal Lookup. It is an Excel function used to search for a value in the first row of a table or an array of data and retrieve a corresponding value from the same column in a specified row.
What is the syntax for the HLOOKUP function in Excel?
The syntax for the HLOOKUP function is as follows:
=HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])
When should I use HLOOKUP in Excel?
HLOOKUP is used when you want to search for a value in the topmost row of a table or range and retrieve a corresponding value from a specific row beneath it. It’s useful for extracting information from horizontally organized datasets.
What error will it show if the lookup value is not found using HLOOKUP?
If the lookup value is not found in the topmost row of the specified range, HLOOKUP returns the `#N/A` error, indicating that the value was not located.
Can HLOOKUP be combined with other functions in Excel?
Yes, HLOOKUP can be combined with other functions like IFERROR to handle errors elegantly, or with functions like MATCH to find the row index number for the lookup dynamically.