sum vlookup results

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

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!
 
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!
 
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!
Click to expand...

Thank you.
 
hi,

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

and

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