Question: How would I go about looking up data in an 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.3

thanks

Answer:

Formula in F1:

=INDEX(tbl, MATCH(y_value, y_header, 0), MATCH(x_value, x_header, 0))

Named ranges
tbl (C4:J7)
y_value (B1)
x_value (D1)
y_header (B4:B7)
x_header (C3:J3)
What is named ranges?

How to customize formula to your workbook
Change named ranges.

Download excel sample file for this tutorial.
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

Related posts:

1. Return row reference of largest to smallest
2. Search for a cell value in an excel table
3. Search for a text string in an excel table
4. Lookup between two lists of data to highlight missing data using conditional formatting in excel
5. How to automatically fill all blanks with missing data or formula
6. Combine data from multiple sheets in excel
7. Create a list with most recent data available in excel
8. Search for multiple text strings in multiple cells and use in data validation in excel
9. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
10. Lookup two index columns in excel