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!

  • paste this in E2 cell:

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


  • 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 at 0:56

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.