HLOOKUP Examples

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Are HLOOKUP Examples In Excel?

HLOOKUP Examples in Excel are the examples of the datasets retrieved using the Horizontal Lookup or the HLOOKUP function in Excel. However, before we consider examples of the HLOOKUP function, we must first understand the formula and the function.

For example, in a dataset, to get the salary of the Emp ID - ID148 we apply the HLOOKUP function in cell A8.

We get the output, as shown above.

The Formula Of HLOOKUP FUNCTION In Excel

The syntax of the HLOOKUP formula is,

HLOOKUP formula

The arguments of the HLOOKUP formula are,

  • 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: It is where our result is in the data table.
  • : 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.
HLOOKUP formula Range
  • The TRUE parameter can be passed as number 1.
  • The FALSE parameter can be passed as number 0.
Table of contents
  • The HLOOKUP examples helps us understand one of the important LOOKUP functions i.e., the horizontal or HLOOKUP formula.
  • 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.
  • We also have another function, the XLOOKUP, which combines the HLOOKUP and the VLOOKUP functions.
  • The MATCH function returns the row number of supplied values.
  • INDEX + MATCH can be used as an alternative to the HLOOKUP function in Excel.

Download Template

This article must help understand HLOOKUP Examples with its formulas and examples. You can download the template here to use it instantly.

You can download this HLOOKUP Example Excel Template here - HLOOKUP Examples Excel Template

HLOOKUP Function Video Explanation

 

HLOOKUP Examples In Excel

Here are some examples of the HLOOKUP function in Excel.

Example #1

In an HR department we may deal with employees’ information like salary, DOJ, etc. as given in the data below.

HLOOKUP Example 1

It is the master data we have. We have received the “Emp ID” from the finance team, and they have requested their salary information to process the salary for the current month.

HLOOKUP Example 1-1

Here the main the data is in horizontal form, and the request came in vertical format.

Therefore, the steps to apply the HLOOKUP function to fetch the data are,

  1. First, we must open the HLOOKUP formula in the salary column and select the lookup value as “Emp ID.”


    HLOOKUP Example 1-2

  2. The next thing is we need to select the table array, i.e., the main table.


    HLOOKUP Example 1-3

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

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


    HLOOKUP Example 1-4

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


    HLOOKUP Example 1-5

    We are done. We got the value we required through the HLOOKUP function.

    HLOOKUP Example 1-6

    Now, drag the formula to get the result to the rest of the cells.

    HLOOKUP Example 1-7

Example #2 – HLOOKUP + MATCH Formula

We have added the department against each employee’s name here for the same data as above.

HLOOKUP Example 2

We have another table that requires all the information based on the Emp ID, but all the data columns are not in order.

HLOOKUP Example 2-1

If we manually supply row numbers, we must 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.

HLOOKUP Example 2-2

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.

HLOOKUP Example 2-3

Mention the final argument and close the formula.

HLOOKUP Example 2-4

We get the following result.

HE Output 2-5

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

HE Output 2-6

One problem here is we do not get the format for the date column. So, we need to use the date format in excel manually.

HE Output 2-7

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

HE Output 2-8

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

We can apply the MATCH + INDEX function as the alternative to get the result instead of the HLOOKUP function. Let us consider the below screenshot of the formula.

HE Output 3

The output is given below:

HE Output 3-1

Important Things To Note

  • We will get an #N/A error if the lookup_value is not the exact value in the data table.
  • If we do not provide the value as 0 or 1 for the last argument in the HLOOKUP formula, by default, it will take 0, and return an exact match.
  • If the row index number is not in the range formula, we will get the #REF error.

Frequently Asked Questions

1. Where is the HLOOKUP function found in Excel?

The HLOOKUP function is found as follows:
First, choose an empty cell - select the “Formulas” tab - go to the “Function Library” group - click the “Lookup & Reference” option drop-down - select the “HLOOKUP” function, as shown below.

Hlookup Formula

2. Where is the VLOOKUP function found in Excel?

The VLOOKUP function is found as follows:
First, choose an empty cell - select the “Formulas” tab - go to the “Function Library” group - click the “Lookup & Reference” option drop-down - select the “VLOOKUP” function, as shown below.

Vlookup Function

3. Where is the INDEX function found in Excel?

The INDEX function is found as follows:
First, choose an empty cell - select the “Formulas” tab - go to the “Function Library” group - click the “Lookup & Reference” option drop-down - select the “INDEX” function, as shown below.

Index Formula

4. Where is the MATCH function found in Excel?

The MATCH function is found as follows:
First, choose an empty cell - select the “Formulas” tab - go to the “Function Library” group - click the “Lookup & Reference” option drop-down - select the “MATCH” function, as shown below.

Match Function

5. Why is the HLOOKUP function not working?

A few reasons the HLOOKUP function may not work are,
• We are trying to fetch the data from bottom to top. It is not possible because, like VLOOKUP, HLOOKUP has a limitation of fetching the data from top to bottom, not from bottom to top.
• The lookup_value is not found in the table_array or dataset.
• The dataset is modified and the retrieved value where we have applied the formula, did not get updated.
• The existing dataset is deleted or removed. So, the formulas is unable to reference to give the output.