dynamic vlookup then sum

  • Thread starter Thread starter TRB
  • Start date Start date
T

TRB

I have a range of values in a1:a5 as follows:

0
100
0
1000
0
0
500


I have a table in cells b1:c3 as follows:

100 100
500 200
1000 300

Is there a way using a formula (in a single cell) to sum the vlookup
values for the range of values in the range a1:a5? The result I am
seeking from the above data would be 600.
 
Hi TRB,

Try this...

The below formula add the assigned value and get the exact result when
lookup values have the same data for multiple times.

=SUMPRODUCT(SUMIF(B1:B3,A1:A6,C1:C3))

Hope that helps!
 
You can simplify that a tad Isabelle

=SUMPRODUCT(ISNUMBER(MATCH(B1:B3,A1:A7,0))*(C1:C3))
 
Back
Top