Thread: multiply cell value by a number dependent on two criteria

=IF(AND(C1="x",D1="y"),A1,IF(AND(C1="x",D1="l"),A1*2,"???"))

You havent said what happens if C1 not equal "x" and D1 not equal "y" or "l" so I've put "???" instead

mucho thankso... got me headed in right direction... not sure if i can do what i ultimately want...

say c1 and d1 can have the values below, can one function contain all possibilities? (i.e. - 'x''y', 'x''l', 'x''r', 'b''y','b''l','b''r', etc.) with each condition met resulting in a certain number to multiply cell a1 by? i was able to add a third criteria successfully with the function below, but when i try to extend it using "b" as c1 value and continuing through the d1 values it says i have entered too many arguments for the function.

maybe i had the function entered incorrectly, or perhaps i am asking too much from the program with so many variables in one function?

i can always just use the function you provided on a worksheet that only contained "x" in the c1 cell and run through 3 variables of a d1 cell, then pull up a second worksheet that only contained "b" in c1 cell and run through the d1 variables,etc. etc. and combine end results, but it would save a few steps if it could all be included in one function.

thx again for help provided.


c1 = x,b,c,d

d1 = y,l,r

=IF(AND(C1="x",D1="y"),A1,IF(AND(C1="x",D1="l"),A1*2,IF(AND(C1="x",D1="r"),A1*3)))


edit: this is the function i proposed that resulted in "too many arguments" error

=IF(AND(C1="x",D1="y"),A1,IF(AND(C1="x",D1="l"),A1*2,IF(AND(C1="x",D1="r"),A1*3),IF(AND(c1="b",d1="y "),a1*4))))

I propose you create a small side table consisting of 2 columns. The first column would display all the combinations of C1&D1 combined. The second would show the corresponding multiplication factor.

Then you could use a simple formula such as:

=A1*Vlookup(C1&D1,X1:Y20,2,0)

Where X1:Y20 would be your lookup table you created. Adjust as necessary.