One of the simplest ways to return a result based on matching one criteria is the VLOOKUP. A vlookup is a vertical lookup between two corresponding columns in a data table, where the value returned is based on the first match.

The vlookup works based on the criteria, the lookup range, the column reference (relative to the criteria column) and whether the result needs to be an exact or approximate match.

Syntax

• =VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup])

Explanation

lookup_value: the criteria that you wish to match on
table_array: the table range
column_index_num: the relative location of the column to return a value from
range_lookup: specifies whether to return for an exact or appropriate match

In the screenshot below there is data showing the World Cup Goals by player, country and year. The requirement of the yellow cell is to return the country of the corresponding player in cell H5.

There are two ways the formula can be written.

• =VLOOKUP(H5,C:D,2,FALSE)

or

• =VLOOKUP(H5,C:D,2,0)

The range_lookup can be written as FALSE (0) or TRUE (1). False will return the value based on an exact match, where as true will return a value based on an approximate match.

Usages

Vlookups work best when there is only a one-to-one relationship between the criteria and the result, for example a unique reference such as an employee number can only be allocated to one unique employee.

Limitations

The obvious benefit to using vlookup is the speed and simple formula. This is great for Excel beginners but there are many limitations such as:

• Always return the first result that matches the criteria
• Can only be performed on one criteria
• The columns which the result is returning from must always be to the right of the lookup column
• The column index number is not dynamic (inserting or deleting any columns within the table range will require an update to the column index number

Alternatives

A powerful alternative which overcomes all of the vlookup’s shortcomings is the ‘index-match’ formula.