# 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 '19 at 0:11
• Try to use SUMPRODUCT function. – Lee Jan 29 '19 at 8:56
• Great tip while using excel. Never use vlookup, always use index-match. Much more reliable and versatile. – Michthan Jan 29 '19 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)

In my version of Excel, `SUM()` works just fine (Version 2010 (Build 13328.20292 Click-to-Run)).

However, and I cannot test this, that may ONLY be due to this version having the `SPILL` functionality.

But... that just would mean you need {CSE} or a function that assumes an array, like `SUMPRODUCT()` (which other answers rightfully use). However...

When I use it, I have to use an array for the second factor in it. Can't use a simple 1 or even a {1}, since `VLOOKUP()` (which does NOT deserve being maligned for ANYTHING other than not "looking left") is using an input array. Various ways to build such an array, including simply typing one in {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20} perhaps to handle as many as 20 ingredients. Or our old friend `ROW(1:LEN())` adapted to the circumstances with, perhaps, `COUNTA()` instead of `LEN()`. Except...

It seems my version doesn't like non-SPILL arrays, arrays produced by non-SPILL techniques. So it fails. But one can coerce the situation by using a `SPILL` function, one in fact built specifically for SPILLing.

That is `SEQUENCE()`. I used the following for a simple version of the above so the cell addresses would need adjusting, but otherwise, it's production ready:

``````=SUMPRODUCT(VLOOKUP(D1:D3,A1:B3,2,FALSE),  SEQUENCE(COUNTA(D1:D3),1,1,0)  )
``````

(Source table being A1:B3, items in dish being D1:D3.)

`SEQUENCE()` forces `SUMPRODUCT()` back to handling arrays instead of taking the traditional "first cell in the array ("upper left")" as its only input. The inputs are in a column, so `COUNTA()` comes first, then ",1" and not the usual other way around. You want only 1's in the array, so the start value is the next 1, and you want ONLY 1's so the last parameter, step value, is 0.

Side note: Always liked `VLOOKUP()`'s ability to use an array. Never liked that it could only be "One VLOOKUP, One Array" (the motto of the Texas Array-ngers...). (So no array as an input generating several arrays as outputs: Like here, except maybe the source data included "carbs, protein, fat, fiber, yada, yada, yada" too and one wanted their totals also, all from (and now with `LET()`, "in" too) the single function. Thought about that as I wrote this answer and it occurs to me that the underlying reason for that, whatever it might be, might be at the root of why nice new functions like `XLOOOKUP()` can't produce 2-D output (much less 3-D or 4-D). Hope not, 'cause if the reason here is the reason there, it ain't happening soon that they fix it!