Ultimate Guide on Using Hlookup in Excel

by | Dec 13, 2019 | Data Analytics

8 Min Read. |

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.

Download Detailed Curriculum and Get Complimentary access to Orientation Session

Date: 28th Nov, 2020 (Sat)
Time: 11:00 AM to 12:30 PM (IST/GMT +5:30)
  • 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.

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:

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

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:

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

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

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

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.

The result so obtained in cell B6 is 78.

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:

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?

Download Detailed Curriculum and Get Complimentary access to Orientation Session

Date: 28th Nov, 2020 (Sat)
Time: 11:00 AM to 12:30 PM (IST/GMT +5:30)
  • This field is for validation purposes and should be left unchanged.

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.

Register for FREE Orientation Class on Data Science & Analytics for Career Growth

Date: 28th Nov, 2020 (Sat)
Time: 11:00 AM to 12:30 PM (IST/GMT +5:30)

  • This field is for validation purposes and should be left unchanged.

You May Also Like…

Linear Programming and its Uses

Linear Programming and its Uses

Optimization is the new need of the hour. Everything in this world revolves around the concept of optimization.  It...

An overview of Anomaly Detection

An overview of Anomaly Detection

Companies produce massive amounts of data every day. If this data is processed correctly, it can help the business to...

2 Comments

  1. Anil Kumar

    Nice, informative article. Very helpful thanks for sharing.

    Reply
    • Niharika Mahendra

      Thanks Anil

      Reply

Submit a Comment

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