Hmmm. OK, you can't use range names.

As an alternative you can use:

=INDEX(B2:E5,MATCH(A8,A2:A5,0),MATCH(B8,B1:E1,0))

This assumes you have the data table from below in cells A1:E5, with the
text headers in row 1 and column A. In my example I put one of the text
values in cell A8, the other in B8

See if that helps.


"JR" wrote:

Duke...
Thanks for your reply. I guess what I really need here is help setting up
the formula. Also, perhaps I can visually elaborate. Suppose I have a
matrix on Sheet1 that looks like this:

A B C D E
1 XYZ ABC LMN PDQ
2 XYZ €” 7 20 15
3 ABC 7 €” 8 31
4 LMN 19 7 €” 11
5 PDQ 17 32 11 €”

On Sheet2, I have this:

A B C
1 PDQ XYZ
2 XYZ PDQ
3 PDQ LMN
4 LMN ABC
5 ABC PDQ

I would like a formula in $C on Sheet2 to cross reference the values in
columns $A and $B on the same sheet to the matrix on Sheet1 and automagically
plug-in the value of the intersecting cells.

--
=JR


"Duke Carey" wrote:

If you create names for the rows and columns so that E1:E30 is named "LA" and
A10:L10 is named "SF" you can then obtain the intersection via the formula

=LA SF (note the space between the two)



"JR" wrote:

I want to set up a formula in a cell in one worksheet to display the value of
a cell that is the intersect point of known values in two other cells in
another worksheet (same workbook). E.g., I know the text values of A10
("SF") and E1 ("LA") in Sheet1, so I want the formula in K20 on Sheet2 to
return the static value of E10 (381), which is the intersect point of A10 and
E1 on Sheet1. Thanks for any tips!