Leading the way in Microsoft Office Development

Step 1 - What are Lookup Functions ?

Lookup functions search for values within a table or list

Each lookup function uses a different method for searching and returning a value.

There are three lookup functions:

 -

LOOKUP returns the value in a row or column that matches a value in a row or column.

 -

VLOOKUP returns the value in a column that matches a value in the first column of a table.

 -

HLOOKUP returns the value in a row that matches a value in the top row of a table.

Step 2 - LOOKUP Function

This function returns a particular item from a list.

LOOKUP(lookup_value, array)

LOOKUP(lookup_value, lookup_vector, result_vector)

Step 3 - VLOOKUP Function

This function searches for a value in a particular column and returns an item from the same row.

VLOOKUP(lookup_value, table_array, col_index_num [,range_lookup])

Step 4 - HLOOKUP Function

This function searches for a value in a particular row and returns an item from the same column.

HLOOKUP(lookup_value, table_array, row_index_num [,range_lookup])

Step 5 - Additional Functions

Often the three lookup functions alone do not provide all the necessary options and are often combined with the following functions to improve the lookup capabilities.

 -

CHOOSE returns the value from a list of values, based on an index number.

 -

INDEX returns the value from a table, based on an index number.

 -

MATCH returns the position of a value in an array or list.

 -

OFFSET returns the cell value which is an offset from a given cell reference.

Step 6 - Lookup Wizard

The Lookup Wizard is an additional add-in (available in Excel 2000) that can be used to cross reference a particular column and row from a table.

This wizard will create a formula that is a combination of the INDEX and MATCH functions.

For more information regarding this add-in, please refer to the Lookup Wizard page.