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. Your marketing manager wants to categorize the products by types: Meat, Vegetables, and Drinks. VLOOKUP will come in handy for such data categorization.

You put in this formula: =VLOOKUP(E2,$A$2:$B$14,2,FALSE)

• E2 is the product you want to look up
• $A$2:$B$14 is the category table (absolute reference so it doesn't shift when copied down)
• 2 refers to the second column — the column you want to return data from
• FALSE tells Excel to find an exact match

If your category table is horizontal instead of vertical, use HLOOKUP the same way:

=HLOOKUP(B6,$B$1:$N$2,2,FALSE)

Limitations

VLOOKUP and HLOOKUP have two key limitations. First, they only look in one direction — VLOOKUP can only return values to the right of the lookup column, and HLOOKUP can only return values below the lookup row. Second, they struggle with two-dimensional lookups where you need to match on both a row and a column simultaneously.

A better alternative for data referencing is using the combination of INDEX and MATCH functions, which overcome both of these limitations.