dynamic vlookup then sum

[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.

 

[isabelle]

hi,

=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B3,A1:A6,0)))*(C1:C3))

 

[Sixthsense]

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!

 

[Bob Phillips]

You can simplify that a tad Isabelle

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