nested ifs or vlookup or ?

[Guest]

I need to do some multiplication of a monthly rate and a multiplier based on
eleven different senarios. Each one has to meet two specific catagories.
They are divided by f1, f2, f3, na and then by codes scfh, scvh, scvhr.
Their rate will be dependent on which "f" catagory they are along with which
code they are. I wrote a statement like
=If(and(A2="f1",B2="SCFH",C2*A1,If(andA2="f2",B2="SCVH",C2*B1,If...
This will get me too many ifs...right? I figured there are eleven different
ways for these combinations to go.
I have looked all over the website for answers but either they are way over
my head or I just can't find them. Please help.
Thanks. Maureen

 

[Biff]

Hi!

You should make a table of all the possible combinations and their
associated rates.

The table may look something like this:

A.........B........C
F1.....scfh.....10
F1.....scvh.....10.5
F1.....scvr.....11
F2.....scfh.....10.25
F2.....scvh....10.75
F2.....scvr.....12

Then you *might* be able to use something like this:

=C2*SUMPRODUCT(--(A1:A100="F1"),--(B1:B100="scvr"),C1:C100)

OR, this:

C2*INDEX(C1:C100,MATCH(1,(A1:A100="F1")*(B1:B100="scvr"),0))

The above formula is an array and needs to be entered with the key combo of
CTRL,SHIFT,ENTER.

Biff