HLOOKUP Examples

HLOOKUP Examples in Excel

In this article, we will take examples of HLOOKUP function in excel. Before I give you examples of 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 function.

1. Lookup_Value: This is the value we are considering it as a base value to find the required result.
2. Table_Array: This is the data table which has lookup value as well as result value.
3. Row_Index_Num: This is nothing but in which row our result is there in the data table.
4. [Range_Lookup]: Here we have two parameters 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 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 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. Only thing matters is how is the main data table.

Since our main table is in horizontal table lets apply HLOOKUP to fetch the data.

Step 1: Open the HLOOKUP formula in the salary column and select the lookup value as Emp ID.

Step 2: Next thing is we need to select the table array i.e. main table.

I have locked the main table range by pressing the F4 key. This becomes an absolute reference now.

Step 3: 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.

Step 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 HLOOKUP function.

Drag the Formula to get the result to rest of cells.

Example #2 – HLOOKUP + MATCH Formula

I will take the same data for example but here I have added department against each employee name.

I have another table which 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 number 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 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 excel.

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

Example #3 – INDEX + MATCH as the Alternative to HLOOKUP

We can apply MATCH + INDEX function as the alternative to get the result instead of 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 horizontal form then HLOOKUP should be applied and if the table is in vertical form then 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.
• MATCH function returns the row number of supplied values.
• INDEX + MATCH can be used as an alternative to 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 downloadable excel template. You may learn more about excel from the following articles –

• P Value Formula
• LOOKUP Formula in Excel
• VLOOKUP with Two Criteria
• VLookup function in VBA