1

I'm trying to create a dynamic supplies cost list to help calculate the costs of our recipes whenever we switch suppliers. The idea is to enter the unit price (say of oil - see screenshot), then use a data validation drop-down list to select the size of the unit, and then have a breakdown of the remaining units automatically calculated based on the selection.

enter image description here

enter image description here

Ex: Canola oil, $17.82 (unit price) at 5 gallons (data validation selection) should automatically have that price broken down in the following columns per lb, per ounce, per kg, per gram, etc.

I've searched for some examples of this already but maybe I'm not using the right search terms because I can't find anything? Any help would be appreciated!

Improve this question
0

1 Answer 1

Reset to default
0
  • paste this in E2 cell:

=ARRAYFORMULA(IFERROR(VLOOKUP(D2:D, 'Units and Measurements'!A3:F, {2,3,4,5,6}, 0)*C2:C))

0

Improve this answer
1
  • 1
    That did it, thanks! I had to reverse the operands like this: =ARRAYFORMULA(IFERROR(C2:C/VLOOKUP(D2:D, 'Units and Measurements'!A3:F, {2,3,4,5,6}, 0))) and it worked like a charm!
    – Kyle Andrew
    Mar 25, 2019 at 0:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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