# A to Z of Excel Functions: the HLOOKUP Function

13 April 2020

*Welcome back to our regular A to Z of Excel Functions blog. Today we look at the HLOOKUP function. *

**The HLOOKUP function**

Often we need to look up data in a table or list – and one such function many are familiar with is **HLOOKUP**. But do you realise it’s very easy to make a mistake with this function.

**HLOOKUP** has the following syntax:

**HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])**

**lookup_value:**what value do you want to look up?**table_array:**where is the lookup table?**row_index_number:**which row has the value you want returned?**[range_lookup]:**do you want an exact or an approximate match? This is optional and to begin with, I am going to ignore this argument exists.

**VLOOKUP** is similar, but works on a column, rather than a row, basis.

*Example*

**HLOOKUP** always looks for the **lookup_value** in the first row of a table (the **table_array**) and then returns a corresponding value so many rows below, determined by the **row_index_number**.

In this above example, the formula in cell **G25** seeks the value 2 in the first row of the table **G12:M18** and returns the corresponding value from the seventh row of the table (returning 96). You can follow all of these examples in the attached Excel file.

Pretty easy to understand; so far so good. So what goes wrong? Well, what happens if you add or remove a row from the table range?

Adding (inserting) a row gives us the wrong value:

With a row inserted, the formula contains hard code (8) and therefore, the seventh row (row 18, not row 19) is still referenced, giving rise to the wrong value. Deleting a row instead is even worse:

Now there are only six rows so the formula returns *#REF!* Oops.

It is possible to make the row index number dynamic using the **ROWS** function (that’s right, every **ROWS** has its **HLOOKUP** function):

**ROWS(reference)** counts the number of rows in the **reference**. Using the range **F12:F18**, this formula will now keep track of how many rows there are between the lookup row (12) and the result row (18). This will prevent the problems illustrated above.

But there are more issues. Consider duplicate values in the lookup row. With one duplicate, the following happens:

Here, the second value is returned, which might not be what is wanted. With two duplicates:

Ah, it looks like it might take the last occurrence. Testing this hypothesis with three duplicates:

Yes, there seems to be a pattern: **HLOOKUP **takes the last occurrence. Better make sure:

Rats. In this example, the value returned is the fifth of six. The problem is, there’s no consistent logic and the formula and its result cannot be relied upon. It gets worse if we exclude duplicates but mix up the lookup row a little:

In this instance, **HLOOKUP** cannot even find the value 2!

So what’s going on? The problem – and common modelling mistake – is that the fourth argument has been ignored:

**HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])**

**[range_lookup] **appears in square brackets, which means it is optional. It has two values:

**TRUE**: this is the default setting if the argument is not specified. Here,**HLOOKUP**will seek an approximate match, looking for the largest value less than or equal to the value sought. There is a price to be paid though: the values in the first row must be in strict ascending order – this means that each value must be larger than the value before, so no duplicates.

This is useful when looking up postage rates for example where prices are given in categories of pounds and you have 2.7lb to post (say). It’s worth noting though that this isn’t the most common lookup when modelling.**FALSE**: this has to be specified. In this case, data can be any which way – including duplicates – and the result will be based upon the first occurrence of the value sought. If an exact match cannot be found,**HLOOKUP**will return the value*#N/A*.

And this is the problem highlighted by the above examples. The final argument was never specified so the lookup row data has to be in strict ascending order – and this premiss was continually breached.

The robust formula needs both **ROWS **and a fourth argument of **FALSE** to work as expected:

This is a very common mistake in modelling. Using a fourth argument of **FALSE**, **HLOOKUP** will return the corresponding result for the first occurrence of the **lookup_value**, regardless of number of duplicates, errors or series order. If an approximate match is required, the data must be in strict ascending order.

**HLOOKUP **is not the simple, easy to use functions people think it is. In fact, it can never be used to return data for rows above.

*Word to the Wise*

**VLOOKUP** works like **HLOOKUP** but hunts out a value in the first column of a table and returns a value so many columns to the right of this reference. However, it has the same limitations and should be used just as carefully.

*We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.*

*A full page of the function articles can be found here. *