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,MATCH(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 <at>yahoo<dot>com
----
"Pittsburgh Jack" <Pittsburgh [email protected]> wrote in
message news:[email protected]...
> 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,MATCH(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 <at>yahoo<dot>com
> ----
> "Pittsburgh Jack" <Pittsburgh [email protected]> wrote in
> message news:[email protected]...
> > 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 [email protected]> wrote in
message news:[email protected]...
> 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] **** 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 ****
OR
=**** Beth

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

RD

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


"Pittsburgh Jack" <Pittsburgh [email protected]> wrote in
message news:[email protected]...
> 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 <at>yahoo<dot>com
----
"Pittsburgh Jack" <[email protected]> wrote in
message news:[email protected]...
> 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 <at>yahoo<dot>com
----