Hi,

I'm trying to build an index-match formula that involves multiple criteria and the criteria refer to the same columns between the two things being matched. For example, I have the following sample table.


Ingredient Ingredient_Enhancer Meal Time Location Calories Calories_Contributed_by_Stew
Butter Pasta Breakfast Home 5
Rosemary Steak Breakfast Home 10
Carrots Celery Soup Breakfast Home 15
Celery Stew Breakfast Home 20 40
Potatoes Celery Fried Rice Breakfast Home 25
Butter Pasta Lunch Home 30
Rosemary Steak Lunch Home 35
Carrots Celery Soup Lunch Home 40
Celery Stew Lunch Home 45 40
Potatoes Fried Rice Lunch Home 50
Butter Pasta Breakfast Restaurant 55
Rosemary Steak Breakfast Restaurant 60
Carrots Celery Soup Breakfast Restaurant 65
Celery Stew Breakfast Restaurant 70 65
Potatoes Celery Fried Rice Breakfast Restaurant 75

I want to build a formula in the "Soup Calories by Stew" column where the following conditions are met:

For each Stew record, enter in the "Calories Contributed by Stew" column the sum of all Calories for which:
(1) the Stew's Ingredient (ie. "Celery) is found in the Ingredient Enhancer for other Meals;
AND (2) the Time of those other Meals match the Time of the Stew;
AND (2) the Location of those other Meals match the Location of the Stew

For example, the "Calories Contributed by Stew" for the Breakfast Stew is 40 because it is the sum of the Calories of the Breakfast Soup and the Breakfast Fried Rice, both of whose Ingredient Enhancer (ie. "Celery") match the Ingredient of the Stew, both of whose Time (ie. "Breakfast") match the Time of the Stew, and both of whose Location (ie. "Home") match the Location of the Stew.

I tried this formula but it doesn't work.

=INDEX(SUM(Calories),MATCH(Ingredient,Ingredient_Enhancer,Time,Time,Location,Location,0))

Your help will be appreciated.