sum vlookup results

[TRB]

Dear friends:

I have the following array of values in A1:A10

0
0
400
0
1000
0
0
1950
0

I have the following table in B1:C5:
1 10
501 20
1001 30
2001 40
3001 50

I am trying to find a formula that will sum the values returned if you
were to take each value in the array A1:A10 and put them in the
vlookup function separately using the table in B1:C5. The desired
value for the above data would be = 60 (10+20+30, corresponding to the
vlookup of 400, 1000, and 1950)

Any help would be much appreciated!

 

[isabelle]

hi,

you had two responses on this topic to your first post, do you see those ?

 

[Sixthsense]

Hi TRB,

I understand that this post is slightly differed from your previous
post since the present values are approximate matches.

I have created a sample file for you and the link is given below for
your reference.

https://docs.google.com/leaf?id=0B2...ZDJjNS00OTFiLTkyZjQtODlkZmNmMDgyOWZl&hl=en_US

Click the above link and Click Download.

This time I cant able to make it as single formula and I have used a
helper column for deriving the result.

Hope that helps!

 

[TRB]

hi,

you had two responses on this topic to your first post, do you see those ?

Isabelle, I only saw one response. Can you resend the second?

 

[TRB]

Hi TRB,

I understand that this post is slightly differed from your previous
post since the present values are approximate matches.

I have created a sample file for you and the link is given below for
your reference.

https://docs.google.com/leaf?id=0B2TMxNyEPQ2rYjMzNjBmYTctZDJjNS00OTFi....

Click the above link and Click Download.

This time I cant able to make it as single formula and I have used a
helper column for deriving the result.

Hope that helps!

Thank you.

 

[isabelle]

hi,

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

and

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