Report Value at Row/Col Intersection

Posted by David on May 30, 2001 11:22 AM

Sheet 1 has a table with column headings S1, S2, S3, etc. and row headings labeled W1, W2, W3, etc. (the number to the right of the S or W will not be in any order - i.e. might be S2, S6, S9, etc.). All cells below Sx and to the right of Wx contain different numeric values. On Sheet 2, I'd like to key Sx in one cell, Wx in another cell, then have a third cell return the value in the cell on Sheet 1 where Sx and Wx intersect. Any ideas on a formula for the third cell on Sheet 2 that would accomplish this?

Posted by Mark W. on May 30, 2001 12:12 PM

Well, let's suppose that Sheet1!A1:D4 contains...

{"","S1","S2","S3"
;"W1",1,2,3
;"W2",4,5,6
;"W3",7,8,9}

1. Select cells Sheet1!A1:D4, choose the
Insert | Name | Create... menu command and
check the "Create names in" check boxes for
"Top row" and "Left column". This creates
global references (e.g., W1_, S3_, etc.).
Note: if you sort this table at any time
you must re-Create these named references.

2. On Sheet2 enter "W2" in cell A1 and "S3"
in cell B1.

3. Finally, enter the formula, =INDIRECT(A1&"_") INDIRECT(B1&"_"),
in Sheet2!C2.

Posted by Barrie Davidson on May 30, 2001 12:14 PM

Hi David, try this formula:
=INDIRECT("Sheet1!R"&MATCH(A2,Row_Names,0)+1&"C"&MATCH(A1,Column_Names,0)+1,0)

This formula assumes the following:
Your data sheet is named Sheet1.
Your data sheet has named ranges for your column headers (Column_Names) and for your row headers (Row_Names). These correspond to your Sx and Wx respectively.
You input the column information in cell A1
You input the row information in cell A2

Hope this helps you out.

Regards,
Barrie

Posted by Russell on May 30, 2001 12:19 PM

I would use a combination of VLOOKUP and MATCH. Below is a formula I used on Sheet3 to get the value from sheet one. I had my data in the area C7:G11 (with C7 being blank, and the Sx numbers in row 7). I tried to paste the data below the formula - not sure how it will come out. On the match, I just specified the area where the Sx values are (C8:C11), so I needed to add one to get the correct column for the VLOOKUP. Let me know if you have any questions.

=VLOOKUP(D5,Sheet1!$C$8:$G$11,MATCH(Sheet3!E4,Sheet1!$D$7:$G$7,0)+1,FALSE)

S1 S2 S6 S7
W1 4 5 0 5
W3 7 8 4 4
W4 3 2 0 9
W6 9 1 7 12

-Russell

Posted by Russell on May 30, 2001 12:22 PM

I forgot to say that I had W6 in cell D5, and S6 in cell E4 on sheet3.

Posted by Aladin Akyurek on May 30, 2001 12:52 PM

David

There are a number of ways you can do what you want.
Consider the following sample data that occupy the range on Sheet1:

{"","s1","s2","s3";"w1",1,5,3;"w2",6,2,8;"w3",3,7,2}

On Sheet2

in A1 enter: s2
in B1 enter: w3

in C1 enter: =INDIRECT("Sheet1!"&ADDRESS(MATCH(B1,Sheet1!A2:A4,0)+1,MATCH(A1,Sheet1!B1:D1,0)+1))

=INDEX(Sheet1!B2:D4,MATCH(B1,Sheet1!A2:A4,0),MATCH(A1,Sheet1!B1:D1,0))

Both formulas will report a blank cell at the intersection as 0.

Aladin Sheet 1 has a table with column headings S1, S2, S3, etc. and row headings labeled W1, W2, W3, etc. (the number to the right of the S or W will not be in any order - i.e. might be S2, S6, S9, etc.). All cells below Sx and to the right of Wx contain different numeric values. On Sheet 2, I'd like to key Sx in one cell, Wx in another cell, then have a third cell return the value in the cell on Sheet 1 where Sx and Wx intersect. Any ideas on a formula for the third cell on Sheet 2 that would accomplish this?