How do I create an if function with 9 variables and 6 multipliers?

[Guest]

On my worksheet cell A2 contains the STATE and cell B2 contains the price. I
want to enter a function or formula that will calculate the tax amount in
cell C2 if the state in cell A2 is one of 9 and zero if it is not? NOTE:
the tax percentages are not the same for all 9 states.

Here is what I want to happen:

If A2="IA","MS" or "NC" multiply B2 by 7%, If A2="KY" or "PA" multiply B2 by
6%, If A2="CA" multiply B2 by 8.25%, If A2="NE" multiply B2 by 5.5%, If
A2="WA" multiply B2 by 8.8%, If A2="NY" multiply B2 by 8.625% else 0.

I can get part of the formula for the first 3 states
(=IF(A6="MS",PRODUCT(C6*0.07),IF(A6="IA",PRODUCT(C6*0.07),IF(A6="NC",PRODUCT(C6*0.07))))

But when I attempt to continue on with the rest I get a variety of errors.

Can someone help me figure out how to do this?

Thanks

 

[Don Guillett]

Use a VLOOKUP table so that you can easily change in the future. See HELP
index for VLOOKUP

 

[Myrna Larson]

YOu can't nest 9 IF statements. The limit is 7. You work with this sort of
problem by creating a 2-column table with the state in the first column and
the tax in the 2nd. Let's say you put that table in K1:L9. Then the formula in
C2 is

=B2*IF(ISNUMBER(VLOOKUP(A2,$K$1:$L$9,2,0)),VLOOKUP(A2,$K$1:$L$9,2,0),0)

meaning, look up the state in column 1 of the table. If it returns a number,
use it; when the state isn't found in the table the function returns an error,
N/A, which isn't a number. In that case, use 0.

 

[NHarkawat]

=IF(OR(A2="Ia",A2="ms",A2="nc"),B2*7%,IF(OR(A2="ky",A2="pa",),B2*6%,IF(A2="C
a",B2*8.25%,IF(A2="ne",B2*5.5%,IF(A2="wa",B2*8.8%,IF(A2="ny",B2*8.625%,0))))
))
Since the nested IF limit is at 7 you are pretty cose to max. Vlookup will
be the only alternative if this "IF" get beyond 7