Lookup value in cell and sum in two diff worksheets?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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
Click to expand...
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)
 
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()))
 
=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.
 
Back
Top