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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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 ..
 
Isn't there a simpler way? I don't understand the $A:$A and $1:$1 reference
in the formula.

Pittsburgh Jack
 
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
 
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
 
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
Click to expand...

$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.
 
Back
Top