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.

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

I don't know which formula to use?
 

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.

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.

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.


.