Hi I am need of some assistance and was wondering if anyone can help me. I am creating a spreadsheet for quoting jobs on site and am stuck with a particular part.

I have a cell containing a drop down list of 5 different types of product, of which each one represents a different cost. I need a formula that will calculate the total cost, based on which type of product is chosen, and how many of these will be purchased.

For example;

Product A = £5.00 Product B = £10.00 Product C = £15.00 Product D = £20.00 Product E = £25.00

If someone wishes to purchase 7 of Product B, I need the formula to see that I have selected Product B from my drop down list, and that it needs to then times the value of this product (£10.00) by the number of these they wish to purchase (7). The number of products selected is input into a separate cell.

Can anyone assist?

Can provide screenshots of requested.

Thank you in advance.


The dropdown control requires you to to specify a cell link. When you select something from the dropdown list, the linked cell will be updated with the index of the item you selected, as shown below.

Excel Format Control dialog box

You can now use the INDEX function to pull the corresponding value.

Excel index function

Tip: I usually position the dropdown list so that it obscures the linked cell.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.