Microsoft Office Development and Consultancy

Excel

Why not use VLOOKUP or HLOOKUP ?

The VLOOKUP and HLOOKUP functions are the preferred methods although there are a few restrictions about how they can be used.

For example ??

For more information please refer to the VLOOKUP and HLOOKUP Restrictions page.

What if the data you want to return is to the left of the criteria column?

Lookup in Any Column

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

In this example re-ordering the columns is not an option and therefore the VLOOKUP function cannot be used.

However this can be achieved by using a combination of the INDEX and MATCH functions.

Using INDEX and MATCH

This lets you lookup a value in any column and return the corresponding value in a column (to the right ot left) of the lookup column.

The MATCH() function can be used to find the position of a value in a particular cell range.

This is used to find the position (or row number) of the value you are looking up in the necessary column.

The INDEX() function can be used to

=INDEX(B2:B14,MATCH("match",C2:C14,False),1)

"match" - is the item you want to match.

"table" -

This basically means search the data range and return the relative row number where you find a match.

Using a lookup column on the left

The following formula will lookup a "First Name" and return the corresponding "Last Name".

Using a lookup column on the right

The following formula will lookup a "Last Name" and return the corresponding "First Name".

Lookup Wizard Add-in

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.

Things to Remember

This type of lookup can also be achieved using a combination of the OFFSET() and MATCH() functions. For more details refer to the Lookup in Any Column - OFFSET & MATCH page.