Excel Tip: VLOOKUP or HLOOKUP?

 

lookupFrom 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".

Add comment
Log in or register to post comments
This blog

Popular posts from Glen Feechan's Not Just Numbers blog - The blog for those who know it's not just about the numbers. Typical content included is primarily Excel tips and other comment relevant to those responsible for finance in their business. Glen develops spreadsheets for clients all over the world via needaspreadsheet.com and helps accountancy practices to make better use of Excel through his Excellent Accountancy business.

Get The 5 Excel features that you need to know free, by subscribing to Not Just Numbers here.