Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I don't know which formula to use?
I need help understanding which formula I should use. . . I have built a
table similar to the one below on a sheet of my workbook. On a separate sheet, I need to be able to input ONLY two variables (state and fruit) and return information (the cost) defined in the table below. As an example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Texas to Florida and the formula should immediately update to the value to $6.00. Another example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Bananas to Lemons and then immediately change to the value to $10.00. A B C D 1 Texas California Florida 2 Apples $1.00 $2.00 $3.00 3 Bananas $4.00 $5.00 $6.00 4 Grapes $7.00 $8.00 $9.00 5 Lemons $10.00 $11.00 $12.00 Any help is greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I don't know which formula to use?
You may want to look at using the =index(match()) instructions from Debra
Dalgleish's site: http://contextures.com/xlFunctions03.html and especially: http://contextures.com/xlFunctions03.html#IndexMatch2 Flintstone wrote: I need help understanding which formula I should use. . . I have built a table similar to the one below on a sheet of my workbook. On a separate sheet, I need to be able to input ONLY two variables (state and fruit) and return information (the cost) defined in the table below. As an example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Texas to Florida and the formula should immediately update to the value to $6.00. Another example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Bananas to Lemons and then immediately change to the value to $10.00. A B C D 1 Texas California Florida 2 Apples $1.00 $2.00 $3.00 3 Bananas $4.00 $5.00 $6.00 4 Grapes $7.00 $8.00 $9.00 5 Lemons $10.00 $11.00 $12.00 Any help is greatly appreciated. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I don't know which formula to use?
A couple of other ways would be to use range names from your table.
=SUM(Texas Bananas) Since you want to have changing input... =SUM(INDIRECT(State) INDIRECT(Fruit)) = = = = = = = HTH :) Dana DeLouis On 2/6/2010 11:03 AM, Flintstone wrote: I need help understanding which formula I should use. . . I have built a table similar to the one below on a sheet of my workbook. On a separate sheet, I need to be able to input ONLY two variables (state and fruit) and return information (the cost) defined in the table below. As an example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Texas to Florida and the formula should immediately update to the value to $6.00. Another example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Bananas to Lemons and then immediately change to the value to $10.00. A B C D 1 Texas California Florida 2 Apples $1.00 $2.00 $3.00 3 Bananas $4.00 $5.00 $6.00 4 Grapes $7.00 $8.00 $9.00 5 Lemons $10.00 $11.00 $12.00 Any help is greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I don't know which formula to use?
Don - Thank you! This is exactly what I was looking for!
"Don Guillett" wrote: Where e3 is the fruit and e2 is the state =INDEX(A1:D5,MATCH(E3,A:A,0),MATCH(E2,1:1,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Flintstone" wrote in message ... I need help understanding which formula I should use. . . I have built a table similar to the one below on a sheet of my workbook. On a separate sheet, I need to be able to input ONLY two variables (state and fruit) and return information (the cost) defined in the table below. As an example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Texas to Florida and the formula should immediately update to the value to $6.00. Another example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Bananas to Lemons and then immediately change to the value to $10.00. A B C D 1 Texas California Florida 2 Apples $1.00 $2.00 $3.00 3 Bananas $4.00 $5.00 $6.00 4 Grapes $7.00 $8.00 $9.00 5 Lemons $10.00 $11.00 $12.00 Any help is greatly appreciated. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|