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.

An alternative way to lookup values (i.e. cross reference) in any column (or row) is to combine the VLOOKUP() and MATCH() functions.

The VLOOKUP() function can be used to find the value in a column that matches a value in the first column of a table.

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.

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

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

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.

Cross referencing (or two way lookup) in a table (HLOOKUP and MATCH)

The HLOOKUP() function can be used to find the value in a row that matches a value in the first row of a table.

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.

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

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

Notice that the row identifier is the first argument used.

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

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

Cross referencing (or two way lookup) in a table with duplicates

Lets assume that you are using the VLOOKUP and MATCH combination to perform your cross reference.

If you have any duplicates in your column headings the first matching column will be used.

If you have any duplicates in your row headings the last matching row will be used.