Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
quattro pro converter Excel 2003 | New Users to Excel | |||
H2 get excel to return 6 months ahead ie input jan and it returns | Excel Worksheet Functions | |||
"X" Axis in Excel Charts | Charts and Charting in Excel | |||
How do I get a second Y axis in Excel 2000 | Charts and Charting in Excel | |||
Excel - Formula Query: Search for and Return Value | Excel Worksheet Functions |