CODEWITHSUNDEEP
excelexcel-formulavlookupworksheet-function
user3642384
user3642384

Reputation: 11

VLOOKUP depending on the items in the list

I'm counting calories I consume in a day. I have an Excel file with two sheets:

On sheet1, I choose the food item from drop down list. And type in the quantity I consumed. By using below formula it gets the result from Sheet2 (food list) and multiply it with the quantity I ate and divide it by 100 to get the nutrition information of the amount I consumed. 

=(VLOOKUP($A8,'Food Items'!$B$1:$F$59,3,FALSE))*$B8/100

Sheet2 is "Food Items" with their Calories, Proteins, Fat, Carbohydrates per 100 grams. Some items can't be in grams but in slices or pieces e.g slice of bread. I've mentioned the nutrition detail per slice.

This result is for the food items which are mentioned in Sheet2 per 100 grams.

This formula doesn't applies on food items which are in slices. I know I can change the formula to:

=(VLOOKUP($A8,'Food Items'!$B$1:$F$59,3,FALSE))*$B8

What I want is when I choose a food item from list, it uses the formula accordingly. I don't have to type the formula every time I choose an item from the list.

Upvotes: 1

Views: 271

Answers (1)

David Zemens
David Zemens

Reputation: 53623

With Food Items worksheet, add another column (G, for example). Leave this column blank for items which do not need to be multiplied by 100, and put an "x" in it for food items that do need to be divided by 100.

Then you nest your formulas together. Do the VLOOKUP and return the value from column 3 which will be conditionally divided by either 100 (if an "x" is found in column G), or 1 (if column G is empty).

=VLOOKUP($A8,'Food Items'!$B$1:$F$59,3,FALSE)*($B8/If(VLOOKUP($A8,'Food Items'!$B$1:$G$59,6,FALSE)="x",100,1))

Upvotes: 1

Related Questions