HLookup in Excel
- 1 Comment
I thought a good next post would to add how to use HLookup after posting the VLookup information last time. The two functions are very similar only they do the opposite things with it comes to looking a against a table.
Let’s start by taking a look at the formula for HLookup:
HLookup(lookup_value, table_array, row_index_num, range_lookup)
lookup_value is what you are searching for in the first column of the table_array
table_array is the range you would like to look up a value for. You must include both the column you are looking for a value in and the column of the value you would like to return
row_index_num is the row number in table_array from which the matching value is returned if a match is found. The first column in your table_array range is 1 (Must be positive integer greater than or equal to 1).
range_lookup tells the formula if you are looking for an exact match based on value. Enter FALSE if you want it to find an exact match. Enter True to find an approximate match, in which Hlookup will look for the next largest value that is less than value (Must be sorted in ascending order for approximate match).
Possible Errors You May Receive
#REF – If the column you are indexing is greater than the number of column in you table_array range, you will receive this Error. Also if there is a problem with you table_array reference itself, this error could result
#N/A – If you were looking for an exact match that hlookup cannot find, it will return #N/A. Also if the approximate match cannot find and approximate match it will return #N/A as well
#Value – If you type in a value for an index that is less than 1, you will get this error.
Examples for Hlookup
Example 1: =HLookup(“Shirt”,1:4,3,false) will return “Blue”
Another way to to do example1 is to use a cell reference for value:
=HLookup(B1,1:4,2,false) will return “Blue”
Example 2: =HLookup(“Jacket”,1:4,2,false) will return “#N/A”
Example 3 : =HLookup(“Shirt”,1:4,5,false) will return “#REF”
Example 4: =HLookup(“Hat”,1:4,4,false) will return “4″
HLookup can also reference other sheets or even other workbooks that you need to lookup a value on. All you will need is to include a reference to that worksheet or workbook in your formula. I have found that the easiest way is to highlight the range you want looked up with your mouse while you are typing the formula and Excel will automatically fill in the reference for you.
Use IF Statements and ISNA Function along with HLookup
I will be adding sections to the website on both IF Statements and ISNA to the site, but I personally believe using HLookup and these other functions go hand in hand because I use them together so frequently.
Many times when you are using HLookups for a large range of numbers, your result will come back #N/A but you may not want #N/A in your results. For example, you may want to have the formula return a 0 if the match is not found. To do this, you need to use all these functions together. Below is an example of how this could work for you (using the table from able):
Example 1: =IF(ISNA(HLOOKUP(“Jacket”,1:4,3,false)),”Not Found”,”Found”) will return “Not Found”
Example 2: =IF(ISNA(HLookup(“Shirt”,1:4,3,false)),0,HLookup(“Shirt”,1:4,3,false)) will return “Blue”
Features for Future Posts
One of the features that I will explore in a future post is the ability to lookup not just the first instance that the HLookup and VLookup Function finds but he 2nd or 3rd Etc. I will have to post some other Function How-To pages before I get into that though. Also using the Match() formula can be very useful to dynamically choose which row you want based on the criteria. We will explore Indexes and Array soon. Please use the comment section from my blog post to ask me any questions about this feature or give suggestions.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
More Excel Tips
1 Comments on this post
Trackbacks
-
tripti kumari said:
how will we use both match & hlookup function together?
2) can INDEX function will be used with hlookup function?
September 11th, 2010 at 5:38 am