I have two sets of data. The first is an ever growing list of names, which we'll call Names (not necessarily a named range). Something like:


The second is also a growing set of data, includes (but is not limited to) the above names, and has some corresponding values. We'll call this Data.

John    4
Jack    7
Bill    6
Jane    1
Bob     9
Steve   10

What I'd like to do is iterate through Data, looking any items defined in Names, then summing their corresponding values. In this case, it'd return 29 (4+6+9+10).

I immediately thought to use VLOOKUP, as that's the normal function to use for matters like this. However, VLOOKUP seems to only accept a single value as its search key, so I'm at a loss.

1 Answer 1


Let's say your names are in H1:H4, and your data is in J1:K6. Then to obtain the sum you want use the formula:

=sum(arrayformula(vlookup(H1:H4, J1:K6, 2, false)))

The key to the solution is obviously ARRAYFORMULA, which takes a single-argument VLOOKUP and iterates it over an array of arguments.

  • 1
    @pnuts took me a while to wrap my head around why this was working, but working it is. thanks!
    – Tock
    Commented Jun 2, 2017 at 16:54
  • To accommodate an ever growing list, it should be enough in my formula to replace H1:H4 with H:H, etc.
    – ttarchala
    Commented Jun 3, 2017 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.