Example:

First range (on first worksheet):
Col B has names, Col E has numbers
Scott 20
Clark 25
Jones 30

Second range (on another worksheet):
Col B has names, Col E has numbers
Scott 15
Davis 12
Jones 10

Since Clark is not on the second worksheet, I don't get any results for
Clark using my function....if it works right, it should yield 25, but it
isn't. Here's the function I'm using (I'm also using an ISERROR statement to
avoid N/A# errors from appearing)...what am I doing wrong?

=IF(ISERROR(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4 ,FALSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FAL SE)),"",(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FA LSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FALSE) ))

Thanks for any help anyone can offer

=IF(ISNA(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FA LSE)),0,VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FAL SE))+IF(ISNA(VLOOKUP(C8,'Worksheet2'!$B$11:$M$150, 4,FALSE)),0,VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4 ,FALSE))

Hope I got the edits right on your formula. If not - the logic is that it's
one complete IF() + another complete IF(). You test the first VLOOKUP for
ISNA. If TRUE, i.e., no match, use 0. If FALSE, use the first VLOOKUP
value. Then you add to the first result the second one where you test the
SECOND of your VLOOKUPS for #NA. If no match, add 0, else add the 2d
VLOOKUP()


"Wendy" wrote:

Example:

First range (on first worksheet):
Col B has names, Col E has numbers
Scott 20
Clark 25
Jones 30

Second range (on another worksheet):
Col B has names, Col E has numbers
Scott 15
Davis 12
Jones 10

Since Clark is not on the second worksheet, I don't get any results for
Clark using my function....if it works right, it should yield 25, but it
isn't. Here's the function I'm using (I'm also using an ISERROR statement to
avoid N/A# errors from appearing)...what am I doing wrong?

=IF(ISERROR(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4 ,FALSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FAL SE)),"",(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FA LSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FALSE) ))

Thanks for any help anyone can offer

unless both worksheet one and worksheet 2 have a match your equation will
classify the reulst as an erron
try
=IF(ISERROR(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4 ,FALSE)),if(iserror(VLOOKUP(C8,'Worksheet2'!$B$11: $M$150,4,FALSE)),"",VLOOKUP(C8,'Worksheet2'!$B$11: $M$150,4,FALSE)),(VLOOKUP(C8,'Worksheet1'!$B$11:$M $150,4,FALSE))
"Wendy" wrote:

Example:

First range (on first worksheet):
Col B has names, Col E has numbers
Scott 20
Clark 25
Jones 30

Second range (on another worksheet):
Col B has names, Col E has numbers
Scott 15
Davis 12
Jones 10

Since Clark is not on the second worksheet, I don't get any results for
Clark using my function....if it works right, it should yield 25, but it
isn't. Here's the function I'm using (I'm also using an ISERROR statement to
avoid N/A# errors from appearing)...what am I doing wrong?

=IF(ISERROR(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4 ,FALSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FAL SE)),"",(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FA LSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FALSE) ))

Thanks for any help anyone can offer

Duke's response is better. Mine will only give you the value from one sheet
with the priority the first sheet.

"bj" wrote:

unless both worksheet one and worksheet 2 have a match your equation will
classify the reulst as an erron
try
=IF(ISERROR(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4 ,FALSE)),if(iserror(VLOOKUP(C8,'Worksheet2'!$B$11: $M$150,4,FALSE)),"",VLOOKUP(C8,'Worksheet2'!$B$11: $M$150,4,FALSE)),(VLOOKUP(C8,'Worksheet1'!$B$11:$M $150,4,FALSE))
"Wendy" wrote:

Example:

First range (on first worksheet):
Col B has names, Col E has numbers
Scott 20
Clark 25
Jones 30

Second range (on another worksheet):
Col B has names, Col E has numbers
Scott 15
Davis 12
Jones 10

Since Clark is not on the second worksheet, I don't get any results for
Clark using my function....if it works right, it should yield 25, but it
isn't. Here's the function I'm using (I'm also using an ISERROR statement to
avoid N/A# errors from appearing)...what am I doing wrong?

=IF(ISERROR(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4 ,FALSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FAL SE)),"",(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FA LSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FALSE) ))

Thanks for any help anyone can offer

Thank you so much for your fast reply ... I copied this into my real
spreadsheet application and it works wonderfully! What a great group this is
- especially for a first time user, like me.

"Duke Carey" wrote:

=IF(ISNA(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FA LSE)),0,VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FAL SE))+IF(ISNA(VLOOKUP(C8,'Worksheet2'!$B$11:$M$150, 4,FALSE)),0,VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4 ,FALSE))

Hope I got the edits right on your formula. If not - the logic is that it's
one complete IF() + another complete IF(). You test the first VLOOKUP for
ISNA. If TRUE, i.e., no match, use 0. If FALSE, use the first VLOOKUP
value. Then you add to the first result the second one where you test the
SECOND of your VLOOKUPS for #NA. If no match, add 0, else add the 2d
VLOOKUP()


"Wendy" wrote:

Example:

First range (on first worksheet):
Col B has names, Col E has numbers
Scott 20
Clark 25
Jones 30

Second range (on another worksheet):
Col B has names, Col E has numbers
Scott 15
Davis 12
Jones 10

Since Clark is not on the second worksheet, I don't get any results for
Clark using my function....if it works right, it should yield 25, but it
isn't. Here's the function I'm using (I'm also using an ISERROR statement to
avoid N/A# errors from appearing)...what am I doing wrong?

=IF(ISERROR(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4 ,FALSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FAL SE)),"",(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FA LSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FALSE) ))

Thanks for any help anyone can offer