CODEWITHSUNDEEP
arraysexcelindexingmatchvlookup
WillacyMe
WillacyMe

Reputation: 582

Lookup multiple columns and sum their values in excel

I have the following three names which I would like to lookup their names and sum under cell D2. Spreadhsheet

The lookup table (Table1) Lookup table

Everything I've found online which deals with summing lookup values is to do with summing multiple columns in the lookup table i.e. =SUMPRODUCT(VLOOKUP(D2,Table1,{2,3},0)).

I'm trying to accomplish the following: =VLOOKUP(E5,Table1,2,FALSE)+VLOOKUP(F5,Table1,2,FALSE)+VLOOKUP(G5,Table1,2,FALSE) but wanted to see if there is an easier way possibly with arrays as theis would cause problems when there are hundreds of columns.

I've also tried this with Index/Match but again all the issues I've found online deal with summing columns in the lookup table based on one reference.

Upvotes: 2

Views: 416

Answers (1)

JvdV
JvdV

Reputation: 75840

What's wrong with SUMPRODUCT? You could just use for example:

=SUMPRODUCT((Table1[Name]=A2:C2)*(Table1[Age]))

enter image description here

Let me know if I misinterpreted the question.

Upvotes: 2

Related Questions