Leading the way in Microsoft Office Development

Cross referencing (or two way lookup) in a table

This assumes you want to cross reference by using a value in the first column and a value in the first row.

The VLOOKUP (and HLOOKUP) functions can only lookup values that are in the first column (or row) of a table.

You can lookup values (i.e. cross reference) in any column (or row) but this can only be done by putting two functions together.

There are several ways of obtaining the same result and one way is to combine the MATCH() and OFFSET() functions.

The OFFSET() function can be used to locate the exact cell within the table by offsetting from the top left corner.

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

The following formula will return the cross reference of a particular column heading with a particular row heading.

"top_left_cell" is the cell in the top left corner of the table.

"row_item" is the heading that you want to find in the list of row headings (B3:B12).

"col_item" is the heading that you want to find in the list of column headings (C2:L2).

Notice that the row identifier is the first argument used.

The following formula will return the cross reference of Bristal across and Aberdeen down.

The following formula will return the cross reference of London across and Leeds down.

If you are having trouble creating this formula with all the right cell and range references you can use the Lookup Wizard add-in as an alternative to entering the formula manually.

Things to Remember

www.microsoft.com - Performing Two-Dimensional Lookups