The HLOOKUP in Excel enables finding data in a dataset horizontally. HLOOKUP stands for Horizontal and searches for a value in the top row of a table. The function then returns a value in the same column from a row number that you specify in the table. The article covers the concept, formula, and examples to understand the HLOOKUP in Excel.
The HLOOKUP function is helpful if you are dealing with massive datasets. It allows you to find results quickly and hassle-free. The difference between VLOOKUP and HLOOKUP is that VLOOKUP searches for values in a column, while HLOOKUP searches for values in a row.
You won’t need to skim through rows one by one. The HLOOKUP function helps to overview the data set and quickly find information while avoiding errors with manual tasks.
- Syntax of HLOOKUP
- Explanation of the HLOOKUP function in Excel
- Common mistakes when using the HLOOKUP function
The HLOOKUP function is useful for those Excel documents where titles flow down, and data continues to the right. Before continuing with the explanation – Do you know the VLOOKUP function?
Category: Search and reference functions
Application: HLOOKUP searches the first row of a table or array of values and returns the value in the same column from a specified row.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Explanation of the HLOOKUP function in Excel
The HLOOKUP function has four arguments:
- Lookup_value (required): This is the value we want to find and will be looked for in the first column. We can place the text enclosed in quotes or put the reference to a cell containing the searched value. The HLOOKUP function is not case-sensitive.
- Table_array (required): The second argument refers to the data array where the value we are looking for is located; we have to select the data range.
- Row_index_num (mandatory): Row_index_num refers to the row number from which the matching value will be returned.
The row_index_num of 1 returns the first-row value in table_array, 2 returns the second-row value in table_array, and 3 returns the third-row value in table_array, and so on.
- Range_lookup (optional): This argument is a logical value, i.e., false or true. With this argument, we indicate to the HLOOKUP function the type of search it will carry out and that it can be an exact search (FALSE) or an approximate search (TRUE). If this argument is omitted, a TRUE value is assumed.
Common mistakes when using the HLOOKUP function
- If the key row does not have unique values for each row, the HLOOKUP function will return the first result that matches the searched value.
- If we specify a row indicator greater than the number of rows in the table, we will get a #REF!
- In case we set the row indicator equal to zero, the HLOOKUP function will return a #VALUE!
- If we configure the HLOOKUP function to perform an exact search, but it does not find the value searched for, the function will return a #N/A error.
Example 1 – HLOOKUP Function
Objective: In the below dataset, we will look out for the marks obtained by Joey in General Awareness using the HLOOKUP function.
We will start writing our HLOOKUP formula in an empty cell. We will mention the lookup value, which is Joey, here.
Lookup_value – Joey.
We will have to look through the entire dataset, which spreads from A1 through G5.
table_array = A1:G5
We have to pick the value from the General Awareness row, which is the 5th row.
row_index_num – 5
Now, we need the exact value, and we will use the FALSE command.
Range_lookup – False or 0
Press Enter to see that the HLOOKUP function has fetched the marks obtained by Joey in General Awareness.
Example 2 – HLOOKUP Function
In our next example, we will be using the same dataset. In this case, we will use the HLOOKUP function to find the English marks obtained by a student whose name starts with “P”. Here, we will use an Excel wildcard character, an asterisk (*).
Again, we would need to look through the entire dataset, which spreads from A1 through G5.
table_array = A1:G5
We can see that English marks are mentioned in the fourth row. Here, we need to add the row_index_num –
row_index_num = 4
For this dataset, we will use 0.
Range_lookup – 0
You can see that Phoebe is the person whose name starts with P, and she secured 72 marks in English.
Example 3 – HLOOKUP Function
In the same data set, we have now introduced a new row, and we need to integrate this data into our existing dataset. The HLOOKUP function will help in achieving that.
Again, we will use an Excel wildcard character, dollar ($).
The formula will be:
We will flash-fill the entire row to get the respective marks obtained by the students in the class.
In the above example, the explanation would be –
‘lookup_value’ = B1 (which mentions the first appearance of the student’s name)
‘table_array’ = $J$1:$O$2 (Range of the second table of the students)
‘row_index_num’ = 2 (row number for which HLOOKUP will return the value)
‘range_lookup’ = FALSE or 0
I hope this article helped you understand how to use HLOOKUP in your datasets. It is one of the essential functions for efficient data analysis, and you can learn this by practicing.
Top Trending Articles:
Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst
How to Use HLOOKUP in Excel?
HLOOKUP is a widely used function that is included within the search and Reference functions of the Microsoft Excel program. This is used to do horizontal searches in a row and return the value that corresponds to it in the row below.
What is HLOOKUP used for?
HLOOKUP has many uses: for example, suppose you sell many different products and want to record your sales in a table at the end of the month. It would be very cumbersome to search for each product in a price list to enter the respective price individually. With Excel's HLOOKUP function, you skip this step. Only the products in the price list are required to be arranged horizontally. Excel enters the prices with the appropriate syntax automatically.
When to use the VLOOKUP function in Excel?
The VLOOKUP function searches for a value in the first left column of the selected range/table and returns data from the same row in a specific column in the range.
What is the difference between HLOOKUP and VLOOKUP functions in Excel?
The VLOOKUP function is used when the value sought is in the first left column of the data range, while HLOOKUP looks for a value in the top row of a table and returns the value of the next row in the same column.