Join Digital Marketing Foundation MasterClass worth Rs 1999 FREE

Ultimate Guide on Using Hlookup in Excel

Bannerartboard 18 f83e1ec1c22be2d7e7b7919ebce74d48

Here is a guide for Hlookup in excel for the beginners. You first need to understand the concept of Hlookup in excel and how we can use it for an excel sheet.

An excel sheet contains rows and columns. Take an example where you want to retrieve data from any particular row or a column. It’s a headache to go through all the rows and columns. Here is where the Hlookup and Vlookup Functions into being.

Hlookup stands for Horizontal lookup and Vlookup stands for Vertical lookup. Here we will discuss about Hlookup in excel function and how it is used in an excel sheet for different cases. Hlookup in excel retrieves a value from a row in a corresponding column.

Let’s talk about how lookup tables started. Before the invention of computers lookup tables were used to speed up the calculations.

In computer spreadsheets, lookup tables were the earliest functionality that was used with initial version of VisiCalc in 1979.

Then it was followed by spreadsheets like Microsoft Excel. The first form of lookup tables are Hlookup and Vlookup whereas in Microsoft the Vlookup functionality was started on 28th august 2019. Before talking about Hlookup in excel, you need to know the fundamentals about excel.

Want to Know the Path to Become a Data Science Expert?

Download Detailed Brochure and Get Complimentary access to Live Online Demo Class with Industry Expert.

Date: March 30 (Sat) | 11 AM - 12 PM (IST)
This field is for validation purposes and should be left unchanged.

What is Hlookup in excel?

Hlookup in excel function looks like:

=HLOOKUP(value to look for, table area, row number)

Let us consider examples of Hlookup function in excel. This example would help you to understand what is Hlookup in excel.

Consider an excel sheet that contains marks of students. Below is a table that contains marks of five students in four subjects.

Capture 16 d254eb47afb8fc53bff06aa77234a8a9

How to use Hlookup in Excel?

The above excel sheet clearly shows the marks of students but if you want to retrieve marks in English subject of student whose name is Suraj. You can use Hlookup function as follows:

Capture 17 2c0999773c93189d65014bc5c1506081

Now you need to choose Hlookup function to retrieve information about the Student.

Capture 18 f8ba3330fbde346d397836010236955d

Here in the above image you would see three things which are important to retrieve the information in the table. The function is as follows:

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

Lookup_value: Here we want to retrieve information about the student whose name is Suraj. We would write “Suraj” lookup_value.

Table_array: This value includes the rows of data to search the lookup value. Here we would take the table array as A1:F5 because with the reference to cell A1 and F5 we can get the marks of the “Suraj”.

Row_index_num: We can say that row index number the row from which we want to perform the lookup function. Here we want the marks of Suraj in English subject. Thus, the row_index_num is 4 as we are fetching the value from the fourth row of the table. Similarly, if we wanted to retrieve marks of Suraj in SST, we would have taken row_index_num as 5.

When we fill up all the above values our excel sheet would look like the picture given below:

Capture 19 0bc7e2ddb1c94b26448c874aaaca5fbf

The result that you get after pressing the enter key is:

Capture 20 7af1bfe81cc67cd438fce6e46be12f91

Now when we know what is Hlookup in excel, it is very easy to perform but there are some important points that we need to keep in mind:

(i) The Hlookup function makes the lookup easy but is not case sensitive. Hlookup in excel example is if we write “Amy” and “AMY”. Both the letters would be considered the same.

(ii) The “lookup_value” in Hlookup function should be the topmost row of the “table_array”. If you are looking for something extra or something else then you should use another Excel formula.

(iii) Only if “lookup_value” is text, the Hlookup in excel will support wildcard characters like “*” (asterisk mark) or “?” (question mark)

Here is Hlookup in excel example for wildcard character “*”. If we want to retrieve marks of student whose name starts with “S” then we can use the lookup_value as “S*”

Capture 21 9d08eb2e9fa616bb7b403398e010ac84

(i) #N/A error: Another thing in Hlookup function in excel is “range_lookup”. This value can either be “TRUE” or “FALSE”. #N/A error would be returned by Hlookup in excel if the “range_lookup” is FALSE as well as for the given range, Hlookup function is unable to find the “lookup_value”. In such a case you can also include the function named IFERROR. With this IFError function you can display your own message. Take an Hlookup in excel example for IFERROR function as: =IFERROR(HLOOKUP(A5, A1:F5, 4, FALSE), “No value found”. Thus, if Hlookup function will not be able to search the value it would automatically display the message as “No value found”.

(ii) What if the “row_index_num” is less than 1? It is obvious that the Hlookup function would return #VALUE!error.

(iii) What if the row_index_num is greater than the number of columns in the given “table_array”? In this case the Hlookup function in excel returns #REF!error.

(iv) One thing that we need to keep in mind is that Hlookup in excel can only return one value. This value is called the “lookup_value”.

(v) You can consider another case where there are some records that match each other. How to use Hlookup in excel in such a case? What we recommend you in this case is to create a Pivot table so that these records are grouped or you can just remove such records so that they aren’t identical to each other. You can then use the array formula in Pivot table which would help you to retrieve all the duplicate values in the lookup range of excel sheet.

How to use Hlookup in Excel from Another Sheet?

Let’s now consider another Hlookup in excel example which will make a clear view about what is Hlookup in excel, where there are two excel sheets.

Here we are taking an example of the previous table that contains marks of students in different subjects.

Consider two sheets such that Sheet1 is the table that we have considered in previous example and Sheet2 is another table. The below are the images of both the sheets.

Capture 22 77f3cd4e2f022c2cecb39347505588fb

Capture 23 b9891792f7e6be3e20655f2c37642ea8

It is clear from the above images that the marks in Management from sheet2 are in context to marks in sheet1. If we want to extract information from sheet2 and want it to be displayed in sheet1, we will use the following formula:

=HLOOKUP(B1,Sheet2!B1:B2,2)

Here in the above formula the Hlookup in excel will look up the cell B1 first, which will let it know that it needs to retrieve the marks of Rahul, then it would use the table_array in the second sheet which we have used B1:B2 and the row_index_num is 2. This will retrieve the marks of Rahul in Management subject. The Result so obtained is 78 from Sheet2.

The below image shows we have used the Hlookup function to retrieve information from the Sheet2.

Capture 24 f0a6a70ff4e385c0af9fb9f39bb27bf6

The result so obtained in cell B6 is 78.

Capture 25 8c5f3e8726ef070a71f8758047d5c384

Likewise, if we want to retrieve the information in all the cells from the Sheet2 from Sheet1, we will use Hlookup formulas for all the different cells. To retrieve marks of Nisha in Management, we would use the following Hlookup formula:

=HLOOKUP(A1,Sheet2!A1:C2,2)

In this manner we would get the result 82.

Thus, in this manner Hlookup function can be used to retrieve information from the sheet2 to sheet1. Here is the result so obtained:

Capture 26 bd3c3c915aa22052ab72023fe5e63dc7

To understand Hlookup in excel in two different worksheets you can go through this tutorial

How to use Hlookup in excel to retrieve values from a single horizontal lookup?

What do we mean by single horizontal lookup? This means that what if we want the Hlookup function in array form. How to use Hlookup in excel if we want information about a single value. Let us consider an example in which we want to retrieve marks of “Rohit” from the previous table. The formula would contain a little change. Let’s see how we would use the Hlookup formula then?

The formula so used seems to be

=HLOOKUP(“Rohit”A1:F6,{1,2,3,4,5},FALSE)

After writing this formula we need to press CTRL+SHIFT+ENTER instead of pressing only ENTER button because this will encloses the Hlookup function formula inside the curly braces and gives us the result without any error.

In this way we can retrieve all marks of “Rohit” using Hlookup Function.

Important notes that we need to keep in mind for using HLOOKUP in Excel

What is Hlookup in excel? Hlookup is a function of excel that is used to search for a value in the first row of a table. With matched row and matched column, this function retrieves a specific value from the specified row.

People often get confused when to use HLOOKUP function and VLOOKUP function. HLOOKUP function is used when we need to retrieve information about a row and lookup values are located in the first row of a table while VLOOKUP function is used when we need to retrieve information about column and lookup values are located in the first column of a table.

(i) Range_lookup: Range_lookup is used when the value needs to match exactly or not and is optional in many cases. If we do not mention the range_lookup value then it considers TRUE as its default value. This default TRUE value allows a nonexact match.

(ii) If we want an exact match then we need to use a FALSE value to the range_lookup value.

(iii) What does the range_lookup value mean if we set it TRUE. This is the default value of range_lookup value for Hlookup function in excel. It means a non-exact match as discussed in the first point and this causes Hlookup function to match the nearest value in the table which is actually less than the value.

(iv) What happens we omit range_lookup value in hlookup function? It uses a non-exact match but it uses an exact match if it is possible or it exists.

Want to know other advanced excel tricks being a Data Analyst? Take up the Data Analytics Course and elevate your career.

Avatar of neha gyanchandani
Neha Gyanchandani
Neha is an IT Engineer by profession with 5+ years of experience in the IT field. Her knowledge in technical as well as communication skills helped her to work as a Business Analyst and deal with clients across the world. Her love for writing made her work as a writer so that she can share her knowledge and experience with others.

2 thoughts on “Ultimate Guide on Using Hlookup in Excel”

Leave a Comment

Your email address will not be published. Required fields are marked *

In-Demand Courses

4-7 months Instructor Led Live Online Training
Starts March 30, 31, 1, 2, 2024
  • Covers all Digital Marketing Techniques

4 months Online
New Batch Dates are not Open
  • Digital Media Mastery (with Paid Media Expertise)
Digital Marketing Webinars
Mar 30
Upcoming
Raj Sharma, Digital Vidya Team 11:00 AM - 12:00 PM (IST)
Apr 28
Completed
Marketing Leaders from Paytm Insider, Cognizant and Digital Vidya 03:00 PM - 04:00 PM (IST)
Mar 24
Completed
Marketing Leaders from Merkle Sokrati, 3M, Uber India and VIP Industries Limited 03:00 PM - 04:00 PM (IST)

Discuss With A Career Advisor

Not Sure, What to learn and how it will help you?

Call Us Live Chat Free MasterClass
Scroll to Top