How to Use HLOOKUP in Excel

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.

Content

• Syntax of HLOOKUP
• Explanation of the HLOOKUP function in Excel
• Common mistakes when using the HLOOKUP function
• Examples

HLOOKUP Syntax

The HLOOKUP function is useful for 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.

Here’s how it works:

• You tell Excel what value you want to search for.
• Excel searches for that value in the top row of your table.
• Once it finds the value, it returns the data from a specific row below it.

HLOOKUP Formula

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Explanation of the HLOOKUP function in Excel

The HLOOKUP function has four arguments:

1. 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.
2. 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.
3. 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.

4. 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!
• If 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 dataset below, we will look 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.

=HLOOKUP(“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's name starts with P, and she secured 72 marks in English.

Example 3 – HLOOKUP Function

We have introduced a new row in the same data set, and we need to integrate this data into our existing dataset. The HLOOKUP function will help us achieve that.

Again, we will use an Excel wildcard character, dollar ($).

The formula will be:

=HLOOKUP(B1,$J$2:$O$2,2,0)

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 –

=HLOOKUP(B1,$J$1:$O$2,2,0)

‘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 practising. 

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