Drop down menu and product - thank you

[Xample]

Hi guys,

This is my first post here so I hope I will do it ok.

I have a problem that seems to be a little over my head...

I'm trying to make a table that looks like this:

X Y Z Q Rezult


I need X to be a drop down menu with 3 possibilities: A, B, C

Now if X is A the result looks like this:

Result=Y*1.5*Z*Q*20

if X is B the result looks like this:

Result=Y*1*Z*Q*10

if X is C the result looks like this:

Result=Y*0.5*Z*Q*30

I hope it's possible ...


PS. excuse my english

[DonkeyOte]

If we assume X is column A, Y is B etc such that Result is E

E1: =B1*LOOKUP(A1,{"A","B","C"},{1.5,1,.5})*C1*D1*LOOKUP(A1,{"A","B","C"},{20,10,30})

which could be shortened I think to:

E1: =B1*C1*D1*LOOKUP(A1,{"A","B","C"},{30,10,15})

Does that work for you ?

[Xample]

your formula looks like what I want but for some reason there is an error...

I attached the file to make sure I did everything right

[DonkeyOte]

The result I get with

E1: =B1*C1*D1*LOOKUP(A1,{"A","B","C"},{30,10,15})

is 2,700,000

The above is based on UK regional settings - I suspect you may need to alter the delimiters per your own settings (ie use of ; etc...)

[DonkeyOte]

Disregard that Xample - point holds re: regionals BUT the criteria is now "one" "two" "three" as opposed to "A","B","C" ... Lookup generally requires the values be sorted in Ascending order... in this instance rather than re-order such that they appear out of sequence (eg "one","three","two") I would switch approach from LOOKUP to INDEX/MATCH

=B1*C1*D1*INDEX({30,10,15},MATCH(A1,{"one","two","three"},0))

[Xample]

still not working

I attached the print screen with the error

Before this there was selected area with error was this one: {30,10,15} but after I went to Tools->Options->International and I unchecked "Use sistem separators"

Now the selected area with an error is the one in the picture

[DonkeyOte]

see attached - when you open on your own settings the formula should adapt to your settings.

[Xample]

yes...works perfect
I have attached a print screen so you can see how the formula looks with my settings...

many many thanks