Multiple formulas in 1 cell

[davecoppins]

Hope someone can help

I am trying to change the calculation returned in 1 cell depending on the entry of "X" in another

If an "X" is entered in I2 run the calculation in Worksheet1 - =IF(I$2="x",LOOKUP(AY5,'1'!A$3:B$65)+1)*AY5
If an "X" is entered in J2 run the calculation in Worksheet2 - =IF(J$2="x",LOOKUP(AY5,'2'!A$3:B$65)+1)*AY5
If an "X" is entered in K2 run the calculation in Worksheet3 - =IF(K$2="x",LOOKUP(AY5,'3'!A$3:B$65)+1)*AY5

The formula I am trying to use is =IF(I$2="x",LOOKUP(AY5,'1'!A$3:B$65)+1)*AY5 which works ok as it is but I can't figure out how to get the change to happen when putting "X" in J2/J3

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]"X"[/TD]
[TD]"X"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RRP[/TD]
[TD]GP £[/TD]
[TD]GP %[/TD]
[/TR]
[TR]
[TD]=IF(I$2="x",LOOKUP(AY5,'1'!A$3:B$65)+1)*AY5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Thanks in advance

David

 

[Special-K99]

Wrong way round

Maybe
=LOOKUP(AY5,INDIRECT(IF(I$2="X","'1'",IF(K$2="X","'2'","'3'"))&"!A$3:B$65")+1)*AY5

 

[davecoppins]

Wrong way round

Maybe
=LOOKUP(AY5,INDIRECT(IF(I$2="X","'1'",IF(K$2="X","'2'","'3'"))&"!A$3:B$65")+1)*AY5

Thanks for coming back with this

Entering the "X" in I2 and K2 changes the calculation as required but not in J2 and I can't seem to get the additional J2 formula to work

Thanks in advance

David

 

[Special-K99]

Oops, typo!
Should be

=LOOKUP(AY5,INDIRECT(IF(I$2="X","'1'",rIF(J$2="X","'2'","'3'"))&"!A$3:B$65")+1)*AY5

So if I2 is X then return '1'
otherwise
IF J2 is X then return '2'
otherwise return 3 (K2 is X is assumed since I2=X and J2=X both failed if you get this far)

 

[davecoppins]

Lovely

Thanks for your help on this