# Using VLOOKUP for multiple lookup values, then summing the outputs

Example:

I have a sheet listing the number of calories for various foods. 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. 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.

• Do an individual VLOOKUP for each cell in the meal table, then use sum for the total. – fixer1234 Jan 29 at 0:11
• Try to use SUMPRODUCT function. – Lee Jan 29 at 8:56
• Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile. – Michthan Jan 29 at 14:38

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.

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)