How do I do a VLOOKUP from 2 ranges and add the results together?

[Guest]

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,FALSE)),"",(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FALSE)+VLOOKUP(C8,'Worksheet2'!$B$11:$M$150,4,FALSE)))

Thanks for any help anyone can offer

 

[Guest]

=IF(ISNA(VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FALSE)),0,VLOOKUP(C8,'Worksheet1'!$B$11:$M$150,4,FALSE))+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()

 

[Guest]

unless both worksheet one and worksheet 2 have a match your equation will
classify the reulst as an erron
tr
=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))

 

[Guest]

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

 

[Guest]

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.