How can I return a cross referenced cell value?

[Guest]

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!

 

[Guest]

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)

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

Duke... after a deeper read and hitting the help files, I now understand more
about your suggestion to naming the ranges and using the intersection
operator. I've done that and it is working fine, that is, so long as I am on
the same worksheet. I would like to actually perform the intersection
operation on a different worksheet, but don't know how to reference the sheet
that actually has the named ranges.

I'm gonna read some more and hope to figure it out, but could use a tip
here. ;-)

Thanks again!

 

[Guest]

The example of the INDEX() function you suggested works! I've copied the
formula into other cells using absolute cell references (e.g,
=INDEX('Sheet1'!B$2:E$5,MATCH(A1,'Sheet1'!A$2:A$5,0),MATCH(B1,'Sheet1'!B$1:E$1,0))),
and in some cases I'm getting #N/A errors. The values in columns A and B on
Sheet2 are entered from a drop down list; perhaps I have a problem with the
ranges I've named for each list. I think I should be able to figure it out
from here.

Duke, Thank you very much for your replies! You've been incredibly helpful!!