Microsoft Office Development and Consultancy

Excel

VLOOKUP Restrictions

The VLOOKUP() function is extremely useful when working with tables of data however it does have three big restrictions:

1) The lookup column has to be the first column in the table.

Lets consider the following table where we want to lookup a "Last Name" and return an "Age".

In this situation the "table_array" argument must refer to the cell range "C2:E14" (and not "B2:E14", i.e. the whole table).

The following VLOOKUP function looks up "Perry" in the table and returns his "Age".

2) It can only be used to return values in columns to the right of the lookup column.

Lets consider the above table where we want to lookup a "Last Name" and return a "First Name".

This cannot be achieved using the VLOOKUP function.

In this situation you will have to re-order the columns in your table, so the "Last Name" column appears before the "First Name" column.

The following VLOOKUP function looks up "Ellis" in the table and returns his "First Name".

The "Last Name" column is not sorted but luckily there is only one occurrence of Ellis in this column.

3) This function can only be used to find the first occurrence of an item in a column.

HLOOKUP Restrictions

The HLOOKUP() function is also useful when working with tables of data although it shares the same restrictions as the VLOOKUP.

1) The lookup row has to be the first row in the table.

Lets consider the following table where you want to look up a "Last Name" and return an "Age".

In this situation the "table_array" argument must refer to the cell range "B3:H5" (and not "B2:H5", i.e. the whole table).

The following HLOOKUP function looks up "Perry" in the table and returns his "Age".

2) It can only be used to return values in rows below the lookup row.

Lets consider the above table where we want to lookup a "Last Name" and return a "First Name".

This cannot be achieved using the HLOOKUP function.

In this situation you will have to re-order the rows in your table, so the "Last Name" row appears above the "First Name" row.

The following HLOOKUP function looks up "Bell" in the table and returns his "First Name".

The "Last Name" row is not sorted but luckily there is only one occurrence of Bell in this row.

3) This function can only be used to find the first occurrence of an item in a row.