|
How do I do a VLOOKUP from 2 ranges and add the results together?
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 |
| All times are GMT +1. The time now is 02:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com