CODEWITHSUNDEEP
excelexcel-formula
m.vesali
m.vesali

Reputation: 25

Multiple Lookup between tables

please guide me.
I have trouble choosing the formula for the score column.
To better understand, I took a screenshot of my work environment.
Is there a solution? enter image description here

Upvotes: 0

Views: 49

Answers (2)

Ben
Ben

Reputation: 493

Slightly simplified version of BigBen's answer that doesn't required Dynamic Array formulas:

=SUMPRODUCT((Table1[[#Headers],[medal 1]:[medal 4]]="medal "&Table2[medal number])*(Table1[@[medal 1]:[medal 4]]*Table2[score]))

enter image description here

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

SUMPRODUCT with Simple LOOKUP:

=SUMPRODUCT(Table2[@[Medal 1]:[Medal 4]],LOOKUP(Table2[[#Headers],[Medal 1]:[Medal 4]],Table3[Medal],Table3[Score]))

enter image description here

Upvotes: 5

Related Questions