Question: How would I go about looking up data in a cross-reference table.
I have the header row (i.e. 24) value and the column (mm) value and want to return the x/y value. i.e I have 25/X and 9/Y item and want 1.8 to be returned.
(mm) 22 23 24 25 26 27 28 29
8 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
9 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
10 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
11 2.2 2.8 2.8 2.8 2.8 2.8 3.3 3.3thanks

You can't use VLOOKUP in this case, you need to do two different lookups to locate the value you want based on coordinates. A two-way lookup.

Formula in D11:

=INDEX(D4:K7, MATCH(D10, C4:C7, 0), MATCH(D9, D3:K3, 0))

If the x or y value is not found the formula returns #N/A.

Explaining formula in cell D11

Step 1 - Find relative position of y value

MATCH(D10, C4:C7, 0)

becomes

MATCH(9,{8;9;10;11},0)

and returns 2.

Number 9 is found at the second location in this array: {8;9;10;11}.

Step 2 - Find relative position of x value

MATCH(D9,D3:K3,0)

becomes

MATCH(25,{22,23,24,25,26,27,28,29},0)

and returns 4.

Number 25 is at the fourth position in the array.

Step 3 - Get value based on coordinates

=INDEX(D4:K7,2,MATCH(D9,D3:K3,0))

becomes

=INDEX(D4:K7,2,3)

becomes

=INDEX(D4:K7,2,3)

and returns 1.8 in cell D1.

The value is in the fourth column and the second row in cell range D4:K7. INDEX function retrieves that value based on row and column number.

Download excel sample file

looking-up-data-in-an-cross-ref-table
(Excel 97-2003 Workbook *.xls)

Functions in this article

MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range