Lookup value in cell and sum in two diff worksheets?

Say I have three worksheets, two of them contain data which holds names. I
need to sum up a certain persons name as they appear in the two different
worksheets in the third worksheet. How do I go about this? I've tried using
a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE HELP!

Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet
....)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"AZExcelNewbie" <[email protected]> wrote in message
news:[email protected]...
> Say I have three worksheets, two of them contain data which holds names.
I
> need to sum up a certain persons name as they appear in the two different
> worksheets in the third worksheet. How do I go about this? I've tried
using
> a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE
HELP!

I get a nice error message... #N/A

=VLOOKUP(A1,Sheet1!A:A,1)+VLOOKUP(A1,Sheet2!A:A,1)


"Bob Phillips" wrote:

> Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet
> ....)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "AZExcelNewbie" <[email protected]> wrote in message
> news:[email protected]...
> > Say I have three worksheets, two of them contain data which holds names.
> I
> > need to sum up a certain persons name as they appear in the two different
> > worksheets in the third worksheet. How do I go about this? I've tried
> using
> > a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE
> HELP!
>
>
>

That means it is not finding the value in A1 in either sheet1 or sheet2.

What would you like to do in that circumstance, know about it and go fix
that problem, or assume 0?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"AZExcelNewbie" <[email protected]> wrote in message
news:[email protected]...
> I get a nice error message... #N/A
>
> =VLOOKUP(A1,Sheet1!A:A,1)+VLOOKUP(A1,Sheet2!A:A,1)
>
>
> "Bob Phillips" wrote:
>
> > Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second
sheet
> > ....)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "AZExcelNewbie" <[email protected]> wrote in
message
> > news:[email protected]...
> > > Say I have three worksheets, two of them contain data which holds
names.
> > I
> > > need to sum up a certain persons name as they appear in the two
different
> > > worksheets in the third worksheet. How do I go about this? I've
tried
> > using
> > > a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE
> > HELP!
> >
> >
> >

And in this case, since you're matching on text values, I'd assume that you
wanted an exact match.

In general, your =vlookup() formula would look more like:

=vlookup(a1,sheet1!a:b,2,false)

(at least 2 columns (A:B) and bring back the stuff in column B)

so you could ignore the errors with:

=sum(if(iserror(vlookup(a1,sheet1!a:b,2,false)),0,vlookup(a1,sheet1!a:b,2,false)),

if(iserror(vlookup(a1,sheet2!a:b,2,false)),0,vlookup(a1,sheet2!a:b,2,false)))

(all one cell)

Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

AZExcelNewbie wrote:
>
> I get a nice error message... #N/A
>
> =VLOOKUP(A1,Sheet1!A:A,1)+VLOOKUP(A1,Sheet2!A:A,1)
>
> "Bob Phillips" wrote:
>
> > Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet
> > ....)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "AZExcelNewbie" <[email protected]> wrote in message
> > news:[email protected]...
> > > Say I have three worksheets, two of them contain data which holds names.
> > I
> > > need to sum up a certain persons name as they appear in the two different
> > > worksheets in the third worksheet. How do I go about this? I've tried
> > using
> > > a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE
> > HELP!
> >
> >
> >

--

Dave Peterson

=sum(if(iserror(vlookup(a1,sheet1!a:b,2,false)),0,
vlookup(a1,sheet1!a:b,2,false)),
if(iserror(vlookup(a1,sheet2!a:b,2,false)),0,
vlookup(a1,sheet2!a:b,2,false)))

(I got hit by line wrap in my post. This may look a little more clear.

Dave Peterson wrote:
>
> And in this case, since you're matching on text values, I'd assume that you
> wanted an exact match.
>
> In general, your =vlookup() formula would look more like:
>
> =vlookup(a1,sheet1!a:b,2,false)
>
> (at least 2 columns (A:B) and bring back the stuff in column B)
>
> so you could ignore the errors with:
>
> =sum(if(iserror(vlookup(a1,sheet1!a:b,2,false)),0,vlookup(a1,sheet1!a:b,2,false)),
>
> if(iserror(vlookup(a1,sheet2!a:b,2,false)),0,vlookup(a1,sheet2!a:b,2,false)))
>
> (all one cell)
>
> Debra Dalgleish's has some notes you may like:
> http://www.contextures.com/xlFunctions02.html (for =vlookup())
> and
> http://www.contextures.com/xlFunctions03.html (for =index(match()))
>
> AZExcelNewbie wrote:
> >
> > I get a nice error message... #N/A
> >
> > =VLOOKUP(A1,Sheet1!A:A,1)+VLOOKUP(A1,Sheet2!A:A,1)
> >
> > "Bob Phillips" wrote:
> >
> > > Just do a simple VLOOKUP(... in first sheet ...)+VLOOKUP(... in second sheet
> > > ....)
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "AZExcelNewbie" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Say I have three worksheets, two of them contain data which holds names.
> > > I
> > > > need to sum up a certain persons name as they appear in the two different
> > > > worksheets in the third worksheet. How do I go about this? I've tried
> > > using
> > > > a SUMPRODUCT with a VLOOKUP, but I can't seem to get it right, PLEASE
> > > HELP!
> > >
> > >
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson