2

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:

John
Bill
Bob
Steve

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.

4

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 Jun 2 '17 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 Jun 3 '17 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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