How to Use HLOOKUP in Excel

How to Use HLOOKUP in Excel

5 mins read11.6K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 8, 2024 15:39 IST

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. 

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

HLOOKUP Syntax

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

  1. 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.
How to Combine Text Strings in Excel – Shiksha Online
How to Combine Text Strings in Excel – Shiksha Online
Microsoft Excel facilitates complicated calculations and data-heavy evaluations. While analyzing data, you may want to combine several elements into a single result. Excel’s CONCATENATE and & functions make it easy...read more
Tutorial – VLOOKUP in Excel – Shiksha Online
Tutorial – VLOOKUP in Excel – Shiksha Online
VLOOKUP, or vertical lookup, is one of Excel’s most commonly used functions for data analysis. This function belongs to the Search and Reference group, which has other search functions in...read more
IF Functions in MS Excel – Shiksha Online
IF Functions in MS Excel – Shiksha Online
Learning Microsoft Excel is all about adding more and more formulas and functions to your toolkit. To excel in MS Excel, you need to learn more and more formulae and...read more

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.

HLOOKUP Function 1.1

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”,

HLOOKUP Function 1.2

We will have to look through the entire dataset, which spreads from A1 through G5.

table_array = A1:G5

HLOOKUP Function 1.3

We have to pick the value from the General Awareness row, which is the 5th row.

row_index_num – 5

HLOOKUP Function 1.4

Now, we need the exact value, and we will use the FALSE command.

Range_lookup – False or 0

HLOOKUP Function 1.5

Press Enter to see that the HLOOKUP function has fetched the marks obtained by Joey in General Awareness.

HLOOKUP Function 1.6
NOW Function in Excel – Shiksha Online
NOW Function in Excel – Shiksha Online
The article covers the NOW function in MS Excel. It is in continuation to the MS Excel basic functions series.
COUNT Function in Excel – Shiksha Online
COUNT Function in Excel – Shiksha Online
Read on the article to understand how COUNT, COUNTA, and COUNTIF functions work in MS Excel.
MEDIAN Function in MS Excel
MEDIAN Function in MS Excel
The article covers the MEDIAN function in MS Excel. It is in continuation to the MS Excel basic functions series.

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 (*).

HLOOKUP Function 2.1

Again, we would need to look through the entire dataset, which spreads from A1 through G5.

table_array = A1:G5

HLOOKUP Function 2.2

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

HLOOKUP Function 2.3

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.

HLOOKUP Function 2.4
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more
Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more
MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.

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 ($).

HLOOKUP Function 3.1

The formula will be:

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

HLOOKUP Function 3.2

We will flash-fill the entire row to get the respective marks obtained by the students in the class.

HLOOKUP Function 3.3

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 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

FAQs

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.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments