0

I have a basic+ knowledge on Excel, but my problem is a little bit complicated and I would appreciate your help!

I have a table, with names of products, next to each product their amount and their price.

That table, have to be edible from the user (but this on a second time). Next, there is another table, where the user, can select from a drop down list the name of the product that he wants. Then, under that, he puts the amount of that product ex.300.

Now... my problem are the calculations.. I want, when the user select the name of the product, see it's amount and price and find out how it will be the price on the amount the user put..

Example: If the user select the C material and needs 300 amount, I want to make automate a calculation with method of three.
On 960 amount it has 1.66€. On 300 amount, how much?? ->
(300 * 1.66) / 960 = 0.52€

I put also an image if that make it easier to understand it..
https://drive.google.com/file/d/0B64fO-0X1FzOOVN0bC1VU3RLZkE/view?usp=sharing

1

You can do this with VLOOKUP() and INDEX().

First, you should understand how a dropdown (or combo) box works. You specify both the list of items to be displayed and the cell that will hold the output of the selection.

To do this, first create the list or combo box, then right-click on the box and choose "Format Control...". In that dialog, you specify the "Input Range", which is the list of values to be chosen from, and the "Cell Link", which is the cell that will hold the position of the choice in the list - this is always a number. More about list and combo boxes can be found on this support page.

The picture below shows your data, an added combo box and additional cells for calculations. The output of the combo box (the Cell Link) is in D14, and it indicates the chosen material "C" is in the 3rd position of the Input Range. This cell can be hidden or placed out of view.

enter image description here

Because D14 gives only the position of "C" in the list, and not it's value, you can use INDEX() to find the actual value. The formula =INDEX(B2:B12,D14) finds the 3rd item in B2:B12 and returns "C".

Once the material is known, VLOOKUP() can be used to find the associated price or amount. The formula =VLOOKUP("C",B2:D12,2,FALSE) finds "C" in the first column of B2:D12 and returns the value in the second column, 960. See Excel help for more information about these functions.

Combining these two formulas, =VLOOKUP(INDEX(B2:B12,D14),B2:D12,2,FALSE)uses the output of the combo box to find the associated amount. A similar formula gets the price, and this formula in B16:

=B15*VLOOKUP(INDEX(B2:B12,D14),B2:D12,3,FALSE)/VLOOKUP(INDEX(B2:B12,D14),B2:D12,2,FALSE)

calculates the price for the specified amount of the selected material.

  • 1
    Thank you very much for the solution and more for the "lesson"!! I found a lot examples with VLOOKUP, but I couldn't understand how it really works!! – ni gi Jul 30 '17 at 19:49

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.