IF 4 Cases

[Rob #4]

Can someone help me merge these four formulas:

=IF(A3="E",C3*(1+'L&E Rates'!B25),"")

=IF(A3="L",C3*(1+'L&E Rates'!B24),"")

=IF(A3="M",C3*(1+'L&E Rates'!B26),"")

=IF(A3="S",C3*(1+'L&E Rates'!B27),"")

Basically If Cell A3 equals one of those letters I want to multiply C3 by a certain cell on another sheet. Can anyone help me?

 

[Jonmo1]

Try

=INDEX('L&E Rates'!B24:B27,MATCH(A3,{"L","E","M","S"},0))*C3


It will error if A3 doesn't equal any of the 4 choices.

 

[kpark91]

Try

=IF(A3="E",C3*(1+'L&E Rates'!B25),IF(A3="L",C3*(1+'L&E Rates'!B24),IF(A3="M",C3*(1+'L&E Rates'!B26),IF(A3="S",C3*(1+'L&E Rates'!B27),""))))

This will not error but only return empty strings when they do not equal to any of the 4 choices.

 

[Gerald Higgins]

At a very basic level, you can go for nested IFs, like this.

=if(A3="E",C3*(1+'L&E Rates'!B25),if(A3="L",C3*(1+'L&E Rates'!B24),if(A3="M",C3*(1+'L&E Rates'!B26),if(A3="S",C3*(1+'L&E Rates'!B27),""))))

Not tested, but I think this should work.

There are more elegant solutions though.

 

[CharlesChuckieCharles]

Can someone help me merge these four formulas:

=IF(A3="E",C3*(1+'L&E Rates'!B25),"")

=IF(A3="L",C3*(1+'L&E Rates'!B24),"")

=IF(A3="M",C3*(1+'L&E Rates'!B26),"")

=IF(A3="S",C3*(1+'L&E Rates'!B27),"")

Basically If Cell A3 equals one of those letters I want to multiply C3 by a certain cell on another sheet. Can anyone help me?

If you can Index the rates with the letters
as in put "S" in 'L&E Rates'!A27
then you can use a lookup

=If(iserr(Vlookup(A3,'L&E Rates'!$A$1:$B$27,2,FALSE)),"",C3*1+Vlookup(A3,'L&E Rates'!$A$1:$B$27,2,FALSE)

also means if you get more rates you don't have to change your formula

 

[Rob #4]

The first one worked. Thank you