Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Look Up help please
In a spreadsheet as exampled below how can I get Excel to look up a
particular reference and use that in a calculation? e.g. A B C X 4 6 7 Y 2 3 6 Z 7 4 9 So if I had entered in one cell YB it would use the value at their intersection (3 in this case) in further calculations? Hope someone can help. Thanks. Tony -- Tony |
#2
|
|||
|
|||
That is a rather confusing question, because excel rows are labeled with
numbers. So your example would be B2 not YB. If you wanted to do a calculation using the value of cell B2 as a variable, then it would be like this: =B2+8-B2*2 The result would be 7. The equal sign tells excel that you are using a formula, and outputs a result instead of what you type. "Tony" wrote: In a spreadsheet as exampled below how can I get Excel to look up a particular reference and use that in a calculation? e.g. A B C X 4 6 7 Y 2 3 6 Z 7 4 9 So if I had entered in one cell YB it would use the value at their intersection (3 in this case) in further calculations? Hope someone can help. Thanks. Tony -- Tony |
#3
|
|||
|
|||
Tony,
A combination of MATCH and INDEX should do the job. Assuming your "X,Y,Z" is in cells A2-A4 and your "A,B,C" is in cells B1-D1: =INDEX( B2:D4, MATCH("Y",A2:A4,0), MATCH("B",B1:D1,0) ) Replace the "Y" and "B" with references to cells if you want. Hope this helps. Eric "Tony" wrote: In a spreadsheet as exampled below how can I get Excel to look up a particular reference and use that in a calculation? e.g. A B C X 4 6 7 Y 2 3 6 Z 7 4 9 So if I had entered in one cell YB it would use the value at their intersection (3 in this case) in further calculations? Hope someone can help. Thanks. Tony -- Tony |
#4
|
|||
|
|||
Hi Tony,
Try this formula modified from a Google search of INDEX MATCH. =INDEX(B2:D4,MATCH(LEFT(H1,1),A2:A4,0),MATCH(RIGHT (H1,1),B1:D1,0)) Whe B1:D1 = A B C A2:A4 = X Y Z B2:D4 = The numbers H1 = YB returns 3, ZA returns 7, etc. HTH Regards, Howard "Tony" wrote in message ... In a spreadsheet as exampled below how can I get Excel to look up a particular reference and use that in a calculation? e.g. A B C X 4 6 7 Y 2 3 6 Z 7 4 9 So if I had entered in one cell YB it would use the value at their intersection (3 in this case) in further calculations? Hope someone can help. Thanks. Tony -- Tony |
#5
|
|||
|
|||
If you *actually* have a data list set up as you posted, where the cells are
populated: A1 = empty B1 = A C1 = B D1 = CC - (for some reason "C" is reserved) AND A2 = X A3 - Y A4 = Z *AND* <Tools <Options <Calculation tab, "Accept Labels In Formulas" *IS* checked, THEN =Y B *WILL* return a 3 This is called the "intersection operator" and is simply a <Space. (Notice <space between Y & B) So that: =Y B*Z CC Will return 27 Just as: =B Y+CC Z Will return 12 -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Tony" wrote in message ... In a spreadsheet as exampled below how can I get Excel to look up a particular reference and use that in a calculation? e.g. A B C X 4 6 7 Y 2 3 6 Z 7 4 9 So if I had entered in one cell YB it would use the value at their intersection (3 in this case) in further calculations? Hope someone can help. Thanks. Tony -- Tony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|