Google Sheets – Use Array/List as VLOOKUP Search Key

google sheetsvlookup

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.

Best Answer

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.

Related Topic