Excel HLOOKUP Function (Table of contents)
HLOOKUP in Excel
When you are in an interview, which demands excel knowledge it is no surprise that first up they will ask about what is HLOOKUP & VLOOKUP. It is almost an inevitable skill to have whether you like it or not.
When you are working with a small amount of data, it is easy to find anything in the data. However, once the data is getting increased it will be very difficult and time-consuming task for you to look for anything within the data.
That where lookup functions will come into the picture. Excel is a bundle of many formulas which are life to save for many people who work on a large amount of data. Lookup functions will save you valuable time and make you a rock star in the office. In this tutorial, let us go ahead and discuss this in detail.
What is HLOOKUP Function in Excel?
Usually, in excel, we use the terms ROWS & COLUMNS. Have you ever think of an alternative name for these two? Think… Think…
Can we call COLUMNS as VERTICLE and ROWS as HORIZONTAL? The answer is YES!
The reason why I told about this is, that today in this tutorial we are going to discuss HLOOKUP i.e. called “HORIZONTAL LOOKUP”.
If you are a regular user of excel you must have come across a function called VLOOKUP but more often than not HLOOKUP is under-utilized in most of the offices.
HLOOKUP is used to look through the data horizontally and get the desired result based on the rows to number i.e. ROW-INDEX number. As the name itself suggests it will search the data horizontally whereas VLOOKUP searches the data vertically.
HLOOKUP Formula in Excel
HLOOKUP is a lookup function in excel and the HLOOKUP formula is as follows.
- Lookup_Value: Base Value or Criterion Value to search in the table. It is a reference value to the table.
- Table_Array: Table in which your targeted values resides. It is raw data to search the thing you want from the table.
- Row_Index_Num: It represents the row number where your targeted value is there. The first row is 1.
- [Range_Lookup]: It consists of two parameters one is TRUE (1) which looks for an approximate match from the table and another one is FALSE (0) which looks for an exact match from the table.
Note: TRUE represented by number 1.FALSE represented by number 0.
How to Use HLOOKUP Function in Excel?
HLOOKUP function in excel is very simple easy to use. Let us now see how to use HLOOKUP with the help of some examples.
Example #1
Assume you are working in an HR department of the company and you have below data (call as master data) in one sheet and another table in another sheet. Now your manager asks you to get their salary details for that master data. Now you must be wondering how I get the desired result out of it. This function can save your life in these cases.
Below is the master data.
From the above table, you need to get the below employees salary details.
When the data is small, you can just look into the data and enter the salary details manually. However, in case of a large amount of data it does not work like that. So use your life-saving friend HLOOKUP.
Step 1: Select Lookup_Value. Lookup_Value should be unique. There should not be any duplicate values. In the above example, Emp Name has duplicate values so search based on Emp ID.
Step 2: TABLE_ARRAY is your actual or raw data, in this example Master Data. Once you selected the table, you need to lock it by pressing F4 (absolute reference).
Before locking the table range.
After locking the table range.
Step 3: Row_Index_Num is from which row you are looking for the data. In this case, I am looking for salary details, which is the 3rd row. If I am looking for Emp Name then it will be row number 2nd.
Step 4: [Range_Lookup] is TRUE or FALSE. If you are looking for an approximate match then type 1 and if you are looking for exact match use 0. Since I am looking for an Exact match I have mentioned 0.
![[Range_Lookup]](https://cdn.educba.com/academy/wp-content/uploads/2018/10/HLOOKUP-Example-1-6.png)
Result: Now you will get the result and copy paste formula to other cells as well.
Example #2-HLOOKUP with MATCH Function
In the last example, we have seen a detailed explanation of the formula. One major change we can do is we can dynamically get the row_index number.
What Match function does?
Match function can extract me the row number. So I need not enter the row_index number manually.
In the Table1, we have a list of our products. In Table2, we need to find their row number.
Table 1
Table 2
Table 2: Solution
Example #3
Table 1: This table represents Product-wise details.
Table 2: From the above table find out the below details.
If you look at the table all the orders in the rows are changed. By using the Match function, we can get the row_index number.
The result will be like the below one.
Things to Remember about HLOOKUP Function in Excel
- HLOOKUP can give you an error of #N/A if the Lookup_Value is not matching with the table. In the below picture Product-6 is not exists in the raw data table.
- If the duplicate value is found then this function will return the first lookup_values result to the remaining lookup_values.
- You will get an error of #VALUE if Row_Index_Num is less than one.
- If the Row_Index_Num is greater than the number of rows function will give an error type of #REF.
- Use HLOOKUP if the data is in horizontal type and if the data is in vertical format use VLOOKUP.
Recommended Articles
This has been a guide to HLOOKUP. Here we discuss the HLOOKUP Formula and how to use HLOOKUP function along with practical examples and downloadable excel templates. You may also look at these useful functions in excel-
