Intersection of x and y from a table

------------------------------------------------
Hi there!

Here are my two cents!!!

Consider the following table:

A5 A B C D E F

1 p1 q1 r1 s1 t1 u1
2 p2 q2 r2 s2 t2 u2
3 p3 q3 r3 s3 t3 u3
4 p4 q4 r4 s4 t4 u4
5 p5 q5 r5 s5 t5 u5
6 p6 q6 r6 s6 t6 u6
7 p7 q7 r7 s7 t7 u7
8 p8 q8 r8 s8 t8 u8
9 p9 q9 r9 s9 t9 u9
10 p10 p10 r10 s10 t10 u10

x y Intersection
-- --- ------------
4 B q4
7 D s7
9 F u9

If the first "x" row value is in cell B19,
and the first "y" col value is in cell C19,
and the fixed first cell in table is A5 as in the table above (as a starting point of reference),
then
intersection formula on D19 is:
=OFFSET($A$5,MATCH(B19,$A$6:$A$15),MATCH(C19,$B$5:$I$5),1,1)

If you have any questions, post it here or email me. I can probably send you the worksheet where I figured this out.

JJ, if your table won't be sorted after it's
constructed consider using Excel's
Insert | Name | Create... menu command to
establish row and column names that can then be
used to directly access the intersection value.
This approach is very fast because no searching
(LOOKUP or MATCH) is involved. Using your sample
table in cell A1:E4 I selected this range and
choose the Insert | Name | Create... menu command
and opted to create names using the "Top row" and
"Left column". Note: Excel will append an under-
score character to some name candidates to prevent
any conflict with a range or cell address naming
convention. In the case of your data it chose
"c_" instead of "c" to represent cells D2:D4.
Ideally, you should label your tables row and
column headers with something other than a single
letter. Anyway... once you've created these names
you can use them to determine the intersection
value. The formula to find the intersection of
row "f" and column "b" is...

=INDIRECT("f") INDIRECT("b")

Note: The space between the INDIRECT() functions
is Excel's intersection operator.