# sum vlookup results

TRB
Guest
Posts: n/a

 20th Jul 2011
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
Guest
Posts: n/a

 21st Jul 2011
hi,

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

--
isabelle

Sixthsense
Guest
Posts: n/a

 21st Jul 2011
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

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!

TRB
Guest
Posts: n/a

 21st Jul 2011
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?

TRB
Guest
Posts: n/a

 21st Jul 2011
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
>
>
>
> 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.

isabelle
Guest
Posts: n/a

 21st Jul 2011
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?

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post garnm2 Microsoft Excel Worksheet Functions 2 18th Jul 2008 07:56 PM Dewayne Microsoft Excel Worksheet Functions 7 28th Nov 2007 05:53 PM =?Utf-8?B?TGlubg==?= Microsoft Excel Worksheet Functions 1 7th Mar 2007 03:45 AM =?Utf-8?B?UmFuZHkgUiBNdWxsaW5z?= Microsoft Excel Worksheet Functions 3 9th Aug 2006 07:16 PM =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM

Features