HLOOKUP Function

What is the HLOOKUP Function?

HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row.

Formula

=HLOOKUP(value to look up, table area, row number)

How to use the HLOOKUP Function in Excel?

Let us consider the example below. The marks of four subjects for five students are as follows:

Now, if our objective is to fetch the marks of student D in Management, we can use HLOOKUP as follows:

HLOOKUP function in Excel comes with the following arguments:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

As you can see in the screenshot above, we need to give the lookup_value first. Here, it would be student D as we need to find his marks in Management. Now, remember that lookup_value can be a cell reference or a text string, or it can be a numerical value as well. In our example, it would be student name as shown below:

The next step would be to give the table array. Table array is nothing but rows of data in which the lookup value would be searched. Table array can be a regular range or a named range, or even an Excel table. Here, we will give row A1:F5 as the reference.

Next, we would define ‘row_index_num,’ which is the row number in the table_array from where the value would be returned. In this case, it would be 4, as we are fetching the value from the fourth row of the given table.

Suppose, if we require marks in Economics, then we would put row_index_num as 3.

The next is range_lookup. It makes HLOOKUP search for exact or approximate value. As we are looking out for an exact value, it would be False.

The result would be 72.

Here, HLOOKUP is searching for a particular value in the table and returning an exact or approximate value.

Important points to keep in mind about HLOOKUP

1. It is a case-insensitive lookup. It will consider, for example, “TIM” and “tim” as the same.
2. The ‘Lookup_value’ should be the topmost row of the ‘table_array’ when we are using HLOOKUP. If we need to look somewhere else, then we must use another Excel formula.
3. HLOOKUP supports wildcard characters such as ‘*’ or ‘?’ in the ‘lookup_value’ argument (only if ‘lookup_value’ is text).

Let’s understand this using an example.

Suppose we are given names of student and marks below:

If we need to use the Horizontal Lookup formula to find the Math marks of a student whose name starts with a ‘D,’ the formula will be:

The wild character used is ‘*’.

4. #N/A error –  It would be returned by HLOOKUP if ‘range_lookup’ is FALSE and the HLOOKUP function is unable to find the ‘lookup_value’ in the given range. We can embed the function in IFERROR and display our own message, for example: =IFERROR(HLOOKUP(A4, A1:I2, 2, FALSE), “No value found”).

5. If the ‘row_index_num’ < 1, HLOOKUP would return #VALUE! error. If ‘row_index_num’ > number of columns in ‘table_array’, then it would give #REF! error.

6. Remember HLOOKUP function in Excel can return only one value. This would be the first value n that matches the lookup value. What if there are a few identical records in the table? In that scenario, it is advisable to remove them or create a Pivot table and group them. The array formula can then be used on the Pivot table to extract all duplicate values that are present in the lookup range.

To learn more, launch our free Excel crash course now!

HLOOKUP from another workbook or worksheet

It means giving an external reference to our HLOOKUP formula. Using the same table, the marks of students in subject Business Finance are given in sheet 2 as follows:

We will use the following formula:

Then we will drag it to the remaining cells.

Use of HLOOKUP to return multiple values from a single Horizontal LOOKUP

So far, we’ve used HLOOKUP for a single value. Now, let’s use it to obtain multiple values.

As shown in the table above, if I need to extract the marks of Cathy in all subjects, then I need to use the following formula:

If you wish to get an array, you need to select the number of cells that are equal to the number of rows that you want HLOOKUP to return.

After typing FALSE, we need to press Ctrl + Shift + Enter instead of the Enter key. Why do we need to do so?

Ctrl + Shift + Enter will enclose the HLOOKUP formula in curly brackets. As shown below, all cells will give the results in one go. We will be saved from having to type the formula in each cell.

Free Excel Course

Check out CFI’s Free Excel Course if you want to learn more about Excel functions using your own online instructor. You’ll receive step by step instructions and demonstrations on how to become an expert in Excel.

Additional Resources

Thank you for reading CFI’s guide on the HLOOKUP Function. To keep learning and advancing your career, the following resources will be helpful:

• Excel Functions for Finance
• Advanced Excel Formulas Course
• Advanced Excel Formulas You Must Know
• Excel Shortcuts for PC and Mac
• See all Excel resources