HLOOKUP is one of the basic functions in Excel that is used very commonly today. In this article, I will help you better understand the function and how to use it!

## 1. Learn about the Excel HLOOKUP function

The Excel HLOOKUP function finds and retrieves a value from data in a horizontal table. The “H” in HLOOKUP stands for “horizontal” and lookup values should appear in the first row of the table, moving horizontally to the right.

**Syntax:**

**=HLOOKUP (lookup_value, table_array, row_index, [range_lookup])**

Where the function arguments are as follows:

**lookup_value:** The value you want to look up, in the first row of the table

**table_array:** The table or array from which to retrieve data.

**row_index:** The row number in the supplied table from which you want to retrieve data.

**range_lookup:** [optional] Use TRUE or 1 for an approximate match and FALSE or 0 for an exact match.

- TRUE: if the function cann’t find an exact match, it uses an approximate match.
- FALSE: if the function cann’t find an exact match, it will return an error.

## 2. Excel HLOOKUP Function Examples

**Example 1: Student ranking – exact match**

In the example below, we need to rank the students in Table 1 (B3:D8) with the data in Table 2 (B11:F12).

In cell D5, the HLOOKUP formula, copied down, is:

**=HLOOKUP(C5,$G$4:$J$5,2,FALSE)** *// exact match*

The HLOOKUP function will look up the score in cell C5 in Table 2 from left to right. When it finds a value equal to lookup_value, it returns the corresponding rating in row 2.

**Example 2: Student ranking – approximate match**

In the screen above, the goal is to look up the correct Level and Bonus for the sales amounts in C5:C13. To lookup Level, the formula in cell D5 is:

**=HLOOKUP(C5,$H$4:$J$6,2,1)** *// get level*

For each amount in C5:C13, the goal is to find the best match, not an *exact* match.

To lookup Bonus, the formula in cell D5, is:

**=HLOOKUP(C5,$H$4:$J$6,3,1)** *// get bonus*

Note again, range_lookup is set to TRUE or 1 to require approximate match.

## 3. Common errors when using the Excel HLOOKUP function

If you get an error from Excel’s Hlookup function, it may be due to one of the following errors:

### Error #N/A

This is a common error if the HLOOKUP function cannot find a match for the supplied *lookup_value*.

The cause of this usually depends on the value of *[range_lookup]* provided:

- if [Range_lookup] = TRUE (or empty) the #N/A error occurs because the smallest value in the lookup row is greater than the supplied lookup_value.
- if [Range_lookup] = FALSE, the #N/A error occurs because an exact match to lookup_value was not found in the lookup row.

### Error #REF!

Error #REF! usually occurs if the row_index_num argument is greater than the number of rows in the supplied table_array.

## Error #VALUE!

This is a common error due to 2 reasons:

**First:** Since the *row_index_num* argument is < 1 or *not a number.*

**Second:** Because the **[range_lookup]** argument is not recognized as **TRUE** or **FALSE**.

Above, I showed you how to use the **Excel HLOOKUP function**. Hopefully through this article you have understood the syntax and usage of this function so that you can apply it in practice.