Index Match with Multiple Criteria Using Same Criteria Column

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.

Here is a formula that should do what you want:

Please Login or Register  to view this content.

Here is a file that shows how the formula will be applied to column H:
Calorie Counter.xlsx
BTW it seems to me that the calorie count of the breakfast stew at the restaurant should be 140 based on your chart.
Let me know if you have questions.

This worked wonderfully. Thanks!

You're welcome and thanks for the feedback. Please use the thread tools above your first post in the thread to mark the thread 'Solved'. Hope that you have a good day.