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,

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


--
isabelle

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=0B2T...yOWZl&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!

---

Sixthsense


On Jul 20, 8:44*pm, TRB <travisrb...@gmail.com> wrote:
> 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!

On Jul 20, 7:11*pm, isabelle <i...@v.org> wrote:
> hi,
>
> you had two responses on this topic to your first post, do you see those ?
>
> --
> isabelle

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

On Jul 21, 5:18*am, Sixthsense <sixthsense...@gmail.com> wrote:
> 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=0B2T...ctZDJjNS00OTFi....
>
> 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!
>
> ---
>
> Sixthsense
>
> On Jul 20, 8:44*pm, TRB <travisrb...@gmail.com> wrote:
>
>
>
>
>
>
>
> > 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!

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))


--
isabelle


Le 2011-07-21 12:14, TRB a écrit :

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