Multiple condition Situation

[vrachimis]

Hi all.

This is a rather complex situation hopefully someone will be able to help.

I have 3 drop down menus as below.
Cell A1 -Cell with numbers in 50cm increments from 100 to 1000
Cell B1 - Cell with drop down menu which includes 3 types of rails.
Cell C1 - Cell with drop down menu which includes two types of rail sellers.

I need a formula that will calculate the price of the rail depending on the size of the rail, (Cell A1) the type of rail, (Cell B1) and the seller type. (Cell C1). Do note that there are 18 different size options, each with a different price. Each type of rails also carries a different price and each seller sells at a different price. So this means there could be 108 different price results. (18X3X2). So in this case i am not sure if the multiple condition statement would be of any help. I thought about concatenating all 108 options in a list hidden in the spreadsheet and then use a vlookup to get the price. This will most likely work, but i assume there must be a simpler way.
If anyone can assist it would be great.

 

[Trevor_S]

I think this is more down to product pricing than excel. Are these 108 different products each with an individual price and no "logic" to the calculation? If so, then I'd say that your concatenation idea is probably the only approach.
If however there is a logic to the pricing calculation (e.g. twice the length of rail will always be twice the price?), then it should allow a calculation based on a reduced number of options.

 

[vrachimis]

I think this is more down to product pricing than excel. Are these 108 different products each with an individual price and no "logic" to the calculation? If so, then I'd say that your concatenation idea is probably the only approach.
If however there is a logic to the pricing calculation (e.g. twice the length of rail will always be twice the price?), then it should allow a calculation based on a reduced number of options.

Actually the pricing logic is not consistent enough to make things simpler.

 

[mikerickson]

If you list all the 108 possible options, a SUMIFS would return the price for the options chose.