Leading the way in Microsoft Office Development

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

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.

"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).

Remember you always go ACROSS and then DOWN when using this formula.

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.

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

If anyone knows of any advantages or disadvantages with either of these two methods can they please contact us.

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

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.

"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 Aberdeen across and Bristol down.

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

Similarly you could combine the HLOOKUP and MATCH functions.

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).

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.