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

[Guest]

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?

 

[Max]

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

 

[Guest]

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

Pittsburgh Jack

 

[CLR]

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

 

[Ragdyer]

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

 

[Max]

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.

 

[Max]

Typo in line:

If A2 contains: Z, B1 contains: A, C1 returns 8

It should read: