VLOOKUP and HLOOKUP functions
Both VLOOKUP and HLOOKUP are simple and easy to learn. They are good to match data. However, they are not very suitable for data referencing because of their several limitations.
VLOOKUP is similar to HLOOKUP in syntax and uses. VLOOKUP is for lookup of vertical data while HLOOKUP is for lookup of horizontal data.
Syntax
VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Examples
You collected some data of food prices from several supermarkets. You are about to do some analysis by using a pivot table. Your marketing manager wants to categorize the products by types: Meat, Vegetables, and Drinks. So you try to insert a column in your data that will categorize food products into some major food types. VLOOKUP will come in handy for such data categorization. First of all, the data category table (cell A1 to B14) is in “vertical” format. Secondly, it is just simple data matching.
You put in this formula: =VLOOKUP(E2,$A$2:$B$14,2,FALSE)
Cell E2 is the product that you want to look up for the product category.
Cell Range $A$2:$B$14 is the category table from which you want to look up the product category. Please note that I put the absolute referencing for the A2 and B14 cell by inserting the dollar signs because I want to copy this formula down to the remaining data and I want to keep the table referencing to be a constant.
“2” refers to the second column for column A. It tells the referencing position from which you want the data to be looked up.
“FALSE” tells Excel to find an exact match. If you put “TRUE” or omit this value, Excel will instead find the approximate match with lesser value. In this example, we want an exact match.
File to download: LOOKUP examples.xlsx
Related articles: Data Referencing, Dynamic Referencing for Excel, Create a Dropdown List by using Data Validation Command
When you copy the formula down at column G, it will match the value in the look up table (A1:B14).
If you have a category table that is horizontal instead of vertical, you can use the HLOOKUP in a similar way as VLOOKUP example above. The formula will be as below:
=HLOOKUP(B6,$B$1:$N$2,2,FALSE)
We have been so far so good for using VLOOKUP and HLOOKUP for simple data matching. They have been serving our purposes in a simple and elegant way. Let’s look at the below example for data referencing in a two dimensional way. You will then see the inadequacy of the two functions.
If I want to get the Sales Amount under the combination condition of both country and month, using either VLOOKUP or HLOOKUP will barely do the work. I need to combine it with the MATCH function to make it work.
Another limitation of VLOOKUP of HLOOKUP function is that it is a one-way look up. For example, VLOOKUP only looks up data on its right side. The argument “col_index_num” has to be a positive number. If you have data that you want to look up on your left hand side, you have to copy the data from the left to the right. Similar limitation is imposed on HLOOKUP. It can only look up data on the lower side and the argument “row_index_num” has to be a positive number.
A better alternative for data referencing is using the combination of INDEX and MATCH function. Please refer to the Data Referencing article for more information.
