HLOOKUP Examples in Excel

This article will take examples of the HLOOKUP function in Excel. However, before we give you examples of the HLOOKUP function, let us introduce you to the HLOOKUP function first.

The Formula of HLOOKUP FUNCTION in Excel

The formula of the HLOOKUP function includes four 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: We consider this a base value to find the required result.Table_Array: This data table has a lookup value and a result value.Row_Index_Num: This is where our result is 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 sees the exact match from the table.

  • The TRUE parameter can be passed as number 1.The 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 dealing with employees’ information like salary, DOJ,… etc. For example, look at the below data.

It is the master data we have. From the finance team, you have received the “Emp ID.” They have requested their salary information to process the salary for the current month.

Now do not get confused with the data structure here because in the main the data, data is in horizontal form. But, the request came in vertical format.

If you are confused about which formula to apply, the first thing we must look for is the data structure of the main data table. It does not matter if the required table is in vertical form or horizontal form. The only thing that matters is how the main data table.

Example #2 – HLOOKUP + MATCH Formula

For example, we will take the same data, but we have added the department against each employee’s name here.

  • First, we must open the HLOOKUP formula in the salary column and select the lookup value as “Emp ID.” The next thing is we need to select the table array, i.e., the main table. We have locked the main table range by pressing the F4 key. It has become an absolute reference now. We need to mention the row number, from which row of the main table we are looking for the data. In this example, the 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.Now, drag the formula to get the result to the rest of the cells.

We have locked the main table range by pressing the F4 key. It has become an absolute reference now.

We are done. We got the value we required through the HLOOKUP function.Now, drag the formula to get the result to the rest of the cells.

We have another table that requires all the 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 MATCH formula to return the row number based on the column heading.

We must apply the MATCH function in the row index number and automatically get the row numbers. Then, use the formula as shown in the below image.

Mention the final argument and close the formula.

We got the result.

Now, drag the formula to other cells. We will have results.

One problem here is we do not get the format for the date column. So we need to use the 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 manually.

Apply the above format to the date column. We will have the correct date values.

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. For example, look at the below screenshot of the formula.

The output is given below:

Things to Remember about HLOOKUP Examples

  • Note that we may 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 horizontal, then HLOOKUP should be applied. If the table is vertical, then we should use the VLOOKUP function.Like VLOOKUP, HLOOKUP 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

This article is a guide to HLOOKUP Examples in Excel. Here, we discuss examples of the HLOOKUP function and the alternative of HLOOKUP (INDEX + MATCH) in Excel with a downloadable Excel template. You may learn more about Excel from the following articles: –

  • P-Value FormulaP-Value FormulaP value is a statistical measure that helps researchers to determine whether their hypothesis is correct. In addition, it helps determine the significance of the results. The null hypothesis is a default position that there is no relationship between two measured phenomena.read moreLOOKUP Formula in Excel LOOKUP Formula In Excel The LOOKUP excel function searches a value in a range (single row or single column) and returns a corresponding match from the same position of another range (single row or single column). The corresponding match is a piece of information associated with the value being searched.
  • read moreVLOOKUP with Two CriteriaVLOOKUP With Two CriteriaVlookup and Hlookup are referencing functions in excel, used to reference data to match with a table array or a group of data and display the output. The difference between these referencing functions is that Vlookup uses reference columns while Hlookup uses reference rows.read moreVLookup function in VBAVLookup Function In VBAThe functionality of VLOOKUP in VBA is similar to that of VLOOKUP in a worksheet, and the method of using VLOOKUP in VBA is through an application. Method WorksheetFunctionread more