Excel Return of value from junction of x-y axis of a table
 

I have set up a simple information table on an Excel spreadsheet. There are
a series of categories in the left column (for the rows) and a series of
categories along the top of the table (for the colums). How do I get Excel
to return a value from a junction of the X-Y axis by imputing one of the
categories on the left and one of the categories on the top of this simple
table?

One way ..

Assuming this table is in A1: D4 in Sheet1

-- X Y Z
A 5 3 8
B 7 9 9
C 9 4 5

In Sheet2
------------
With cols A and B, in row1 down earmarked for the inputs
of horiz. (X,Y,Z) and vertical (A,B,C) references

Put in say, C1:

=IF(COUNTBLANK(A1:B1)<0,"",OFFSET(Sheet1!$A$1,MAT CH(B1,Sheet1!$A:$A,0)-1,MA
TCH(A1,Sheet1!$1:$1,0)-1))

Copy C1 down

If A1 contains: Y, B1 contains: C, C1 returns 4
If A2 contains: Z, B1 contains: A, C1 returns 8
and so on ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Pittsburgh Jack" <Pittsburgh wrote in
message ...
I have set up a simple information table on an Excel spreadsheet. There
are
a series of categories in the left column (for the rows) and a series of
categories along the top of the table (for the colums). How do I get
Excel
to return a value from a junction of the X-Y axis by imputing one of the
categories on the left and one of the categories on the top of this simple
table?

Isn't there a simpler way? I don't understand the $A:$A and $1:$1 reference
in the formula.

Pittsburgh Jack

"Max" wrote:

One way ..

Assuming this table is in A1: D4 in Sheet1

-- X Y Z
A 5 3 8
B 7 9 9
C 9 4 5

In Sheet2
------------
With cols A and B, in row1 down earmarked for the inputs
of horiz. (X,Y,Z) and vertical (A,B,C) references

Put in say, C1:

=IF(COUNTBLANK(A1:B1)<0,"",OFFSET(Sheet1!$A$1,MAT CH(B1,Sheet1!$A:$A,0)-1,MA
TCH(A1,Sheet1!$1:$1,0)-1))

Copy C1 down

If A1 contains: Y, B1 contains: C, C1 returns 4
If A2 contains: Z, B1 contains: A, C1 returns 8
and so on ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Pittsburgh Jack" <Pittsburgh wrote in
message ...
I have set up a simple information table on an Excel spreadsheet. There
are
a series of categories in the left column (for the rows) and a series of
categories along the top of the table (for the colums). How do I get
Excel
to return a value from a junction of the X-Y axis by imputing one of the
categories on the left and one of the categories on the top of this simple
table?

Maybe check out the INDEX function............

Name the range of your table "MyRange", and do

=INDEX(MyRange,3,3) to get the junction of the third cell down and the third
row to the right.......

Vaya con Dios,
Chuck, CABGx3



"Pittsburgh Jack" <Pittsburgh wrote in
message ...
I have set up a simple information table on an Excel spreadsheet. There
are
a series of categories in the left column (for the rows) and a series of
categories along the top of the table (for the colums). How do I get
Excel
to return a value from a junction of the X-Y axis by imputing one of the
categories on the left and one of the categories on the top of this simple
table?

Included in XL's reference operators, there is the "intersection operator",
which is quite simply, a *space*.

If you had this simple datalist:

A B C D
1] XXX Mary Beth Ann
2] Tom 1 2 3
3] Dick 4 5 6
4] Harry 7 8 9

To return 5, use
=C1:C4 A3:D3
Which is the intersection of the 2 ranges.
Note the space between the ranges.

You could however, also use the names instead.
<Tools <Options <Calculation tab,
And make sure that "Accept Labels in Formulas"
*IS* checked.

Then use this to return 5:

=Beth Dick
OR
=Dick Beth

You can also use the names in actual calculations:
=beth dick*mary harry
to return 35
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Pittsburgh Jack" <Pittsburgh wrote in
message ...
I have set up a simple information table on an Excel spreadsheet. There
are
a series of categories in the left column (for the rows) and a series of
categories along the top of the table (for the colums). How do I get
Excel
to return a value from a junction of the X-Y axis by imputing one of the
categories on the left and one of the categories on the top of this simple
table?

Isn't there a simpler way?

Thought the suggested way wasn't all that tough <g

Perhaps you'd like to try the other suggestions posted by Chuck & Ragdyer.
Just adopt the one that is "simplest" to you or one that you feel most
comfortable with .. The choice is yours.

Just some explanations on:
.. the $A:$A and $1:$1 reference

$A:$A is an entire *col* reference (col A), the dollar signs "$" are meant
to lock the references so that it doesn't change when you copy the formula
across. Col A is where the vertical references are located.

Likewise ..
$1:$1 is an entire *row* reference (row1), the dollar signs "$" are to lock
the references so that it doesn't change when you copy the formula down.
Row1 is where the horizontal references are located.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Pittsburgh Jack" wrote in
message ...
Isn't there a simpler way? I don't understand the $A:$A and $1:$1
reference
in the formula.

Pittsburgh Jack

Typo in line:
If A2 contains: Z, B1 contains: A, C1 returns 8

It should read:
If A2 contains: Z, B2 contains: A, C2 returns 8

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----