HLOOKUP Examples in Excel
In this article, we will take examples of HLOOKUP function in excel. Before I give you examples of the HLOOKUP function, let me introduce you to the HLOOKUP function first.
The Formula of HLOOKUP FUNCTION in Excel
The Formula of the HLOOKUP function includes 4 arguments. All the parameters are the same as the VLOOKUP functionVLOOKUP FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more.
- Lookup_Value: This is the value we are considering it as a base value to find the required result.
- Table_Array: This is the data table that has a lookup value as well as a result value.
- Row_Index_Num: This is nothing but in which row our result is there in the data table.
- [Range_Lookup]: Here, we have two parameters. The first one is TRUE (1), which finds an approximate match from the table, and the second one is FALSE (0), which finds the exact match from the table.
- TRUE parameter can be passed as number 1.
- FALSE parameter can be passed as number 0.
HLOOKUP Examples in Excel
Here are some examples of the HLOOKUP Function in Excel.
HLOOKUP Example #1
Assume you are working in an HR department and you are dealing with employee’s information like salary, DOJ,… etc. For example, look at the below data.
This is the master data you have. From the finance team, you have received the Emp ID, and they have requested for their salary information to process the salary for the current month.
Now don’t get confused with the data structure here because in the main the data, data is there in horizontal form, but the request came in vertical form.
If you are confused about which formula to apply, the first thing you need to look for is what the data structure of the main data table is. It doesn’t matter if the required table is in vertical form or horizontal form. The only thing matters are how is the main data table.
Since our main table is in a horizontal table, let’s apply HLOOKUP to fetch the data.
- Open the HLOOKUP formulaHLOOKUP FormulaHlookup is a referencing worksheet function that finds and matches the value from a row rather than a column using a reference. Hlookup stands for horizontal lookup, in which we search for data in rows horizontally.read more in the salary column and select the lookup value as Emp ID.
- Next thing is we need to select the table array, i.e., the main table.
I have locked the main table range by pressing the F4 key. This becomes an absolute reference now. - Now, we need to mention the row number, i.e., from which row of the main table we are looking for the data. In this example, a row number of the required column is 4.
- The final part is a range lookup. Since we are looking at the exact match, we need to select the option as FALSE or zero (0).
We are done; we got the value we required through the HLOOKUP function.
Drag the Formula to get the result to the rest of the cells.
Example #2 – HLOOKUP + MATCH Formula
I will take the same data, for example, but here I have added department against each employee’s name.
I have another table that requires all the information above information based on the Emp ID, but all the data columns are not in order.
If we manually supply row numbers, we need to keep editing the formula for all the columns. Instead, we can use the formula MATCH, which can return the row number based on the column heading.
In the row index number, apply the MATCH function and get the row numbers automatically. Apply the formula, as shown in the below image.
Mention the final argument and close the formula.
We got the result.
Drag the formula to other cells; we will have results.
One problem here is we don’t get the format for the date column. We need to manually date format in excelDate Format In ExcelThe date format in Excel can be changed either from the “number format” of the Home tab or the “format cells” option of the context menu.read more.
Apply the above format to the date column; we will have correct date values now.
Example #3 – INDEX + MATCH as the Alternative to HLOOKUP
We can apply the MATCH + INDEX functionINDEX FunctionThe INDEX function in Excel helps extract the value of a cell, which is within a specified array (range) and, at the intersection of the stated row and column numbers.read more as the alternative to get the result instead of the HLOOKUP function. Look at the below screenshot of the formula.
The output is given below:
Things to Remember about HLOOKUP Examples
- We will get an error of #N/A if the Lookup_Value is not the exact value in the data table.
- Data table structure matters a lot. If the data table is in a horizontal form, then HLOOKUP should be applied, and if the table is in a vertical form, then the VLOOKUP function should be applied.
- Like VLOOKUP, HLOOKUP, too, has a limitation of fetching the data from top to bottom, not from bottom to top.
- The MATCH function returns the row number of supplied values.
- INDEX + MATCH can be used as an alternative to the HLOOKUP function in excel.
- If the row index number is not in the range formula would return #REF.
You can download this HLOOKUP Example Excel Template here – HLOOKUP Examples Excel Template
Recommended Articles
This has been a guide to HLOOKUP Examples in Excel. Here we discuss examples of HLOOKUP Function and also the Alternative of HLOOKUP (INDEX + MATCH) in excel with a downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
58.6K subscribers