lookup

From the Not Just Numbers blog:

Many of us use VLOOKUP regularly and I know I’ve covered it a number of times on the blog, but what about its poor relation, HLOOKUP?

I regularly come across users who don’t know it exists – or if they do, don’t know what it is for.

This is a shame, as if you understand VLOOKUP, it is very easy to grasp HLOOKUP and sometimes it is exactly what you need.

In this post I will cover VLOOKUP again for completeness and then describe how HLOOKUP differs.

Put simply, HLOOKUP works exactly the same as VLOOKUP, except that it works horizontally rather than vertically (that’s what the V and the H stand for).

Where VLOOKUP enables you to search down a vertical list to return a value from further along that row, HLOOKUP searches along a horizontal list and returns a value from further down that column.

To recap on VLOOKUP:

=VLOOKUP(lookup value,range to lookup,column to return,TRUE or FALSE)

lookup value: This is the value you wish to search the first column of your table for (this can be the actual value, but it can be very powerful to refer to a cell containing the value).
range to lookup: This is the range (either named, or referred to directly) that contains the lookup table. It is useful to refer to whole columns as this allows your list to grow.

column to return: This is given as a number, where the first column (the one being searched) is 1.

TRUE or FALSE: This is a rather odd one. If you enter ‘TRUE’ or omit this argument, Excel will go to the closest value if the actual value being looked up is not there. This requires the first column to be sorted in ascending order. For most financial applications you will need to enter ‘FALSE’ here as this will return an #NA error if it cannot find the lookup value. For a fuller explanation of applications where you would use TRUE, take a look at EXCEL TIP: Look up commission or tax rates from earning bands.

HLOOKUP works in exactly the same way, so for HLOOKUP:
=HLOOKUP(lookup value,range to lookup,ROW to return,TRUE or FALSE)

lookup value: This is the value you wish to search the first ROW of your table for (this can be the actual value, but it can be very powerful to refer to a cell containing the value).

range to lookup: This is the range (either named, or referred to directly) that contains the lookup table. It is useful to refer to whole ROWS as this allows your list to grow.

ROW to return: This is given as a number, where the first ROW (the one being searched) is 1.

TRUE or FALSE: This is a rather odd one. If you enter ‘TRUE’ or omit this argument, Excel will go to the closest value if the actual value being looked up is not there. This requires the first ROW to be sorted in ascending order. For most financial applications you will need to enter ‘FALSE’ here as this will return an #NA error if it cannot find the lookup value. For a fuller explanation of applications where you would use TRUE, take a look at EXCEL TIP: Look up commission or tax rates from earning bands.

HLOOKUP deals with a situation where your data is in columns and you want to pull data from a column based on the column heading.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.