Your example is somewhat ambiguous - is it always the case that a "Y" in
A2 adds $500 to C5?

In that case, here's one way:

=LOOKUP(C2,{"corporate",2000;"enterprise",3000;"sm all biz",1000}) +
500 * (A2="Y")

Or, if not, you could set up a table, say, on Sheet2:

A B C
1 small biz 1000 1500
2 corporate 2000 2500
3 enterprise 3000 3500

and use

=VLOOKUP(C2,Sheet2!A:C,2+(A2="Y"),FALSE)




In article ,
ringo tan wrote:

Hi,

I have a cell say "C2" containing a dropdown list for "small biz",
"corporate" and "enterprise". I have another cell say "C5" that refers to
cell "C2".

a. If user select "small biz" in C2, C5 will show a price of say $1000.
b. If user select "corporate" in C2, C5 will show a price of say $2000.
c. If user select "enterprise" in C2, C5 will show a price of say $3000.


Now, i need to add another condition to the above.
I have another cell A2 for user to enter a "Y" or leave as blank.

d. If user select "small biz" in C2 AND also enter a "Y" in A2, C5 will show
a price of say $1500.
e. If user select "corporate" in C2 AND also enter a "Y" in A2, C5 will show
a price of say $2500.
f. If user select "enterprise" in C2 AND also enter a "Y" in A2, C5 will
show a price of say $3500.

Can anyone give advice on how to write a function to incorporate all 6
conditions namely a,b,c,d,e,f in cell "C5", so that with or without "Y" in A2
+ the result user selected in cell C2 in order to produce 6 different prices
of $1000,$1500,$2000,$2500,$3000,$3500?


Thanks in advance.


Ringo