Using VLOOKUP for multiple lookup values, then summing the outputs

2

1

Example:

I have a sheet listing the number of calories for various foods.

Calorie Data

In another sheet I have a list of some of those foods to be used in a meal, and would like to calculate the total number of calories for that particular meal.

Meal

How can I lookup the calorie data for each food item in the meal, and then sum each of those values together?

I thought something like =SUM(VLOOKUP(A2:A4,$Sheet1.A2:B13,2,0)) might work but alas not.

user991200

Posted 2019-01-29T00:00:23.407

Reputation: 21

3Do an individual VLOOKUP for each cell in the meal table, then use sum for the total. – fixer1234 – 2019-01-29T00:11:04.997

Try to use SUMPRODUCT function. – Lee – 2019-01-29T08:56:00.450

Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile. – Michthan – 2019-01-29T14:38:59.630

Answers

4

Sum a SUMIFS:

=SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5))

This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.

Scott Craner

Posted 2019-01-29T00:00:23.407

Reputation: 16 128

2

Use the B column to grab your calorie count with vlookup, then sum those at the bottom of the column.

B cells next to meal consumed:

=vlookup(A2,$Sheet1!A:B,2,0)

Then a simple sum of the column:

=sum(B2:B5)

Your function above may work as an array function by using cntrl-shift-enter to finalize it and changing your period to an exclamation mark. (untested)

Brian

Posted 2019-01-29T00:00:23.407

Reputation: 681