How can I return a cross referenced cell value?
 

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!

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!

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!

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!

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!

--
=JR


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!

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!!

--
=JR


"Duke Carey" wrote:

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!