|
ISNA with VLOOKUP against 2 locations
I have a new problem I need help with. I am trying to look up a value found
in workbook #1. I need to lookup the value in workbook #2 and, if it's not found there, then lookup the value in a different tab of workbook #1. Here is what I have so far. equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,5,FALSE)) The 2nd part of the formula works fine as the lookup returns the appropriate P8 value from the tab in workbook 1 when it is not found in workbook2. However, when the value IS found in workbook2, it returns a value of FALSE, not the appropriate lookup value. Should I be using a different IS function? Any assistance would be greatly appreciated!! -- Thanks so much! |
ISNA with VLOOKUP against 2 locations
Try this:
=lIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,5,0),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)) -- Biff Microsoft Excel MVP "steph" wrote in message ... I have a new problem I need help with. I am trying to look up a value found in workbook #1. I need to lookup the value in workbook #2 and, if it's not found there, then lookup the value in a different tab of workbook #1. Here is what I have so far. equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,5,FALSE)) The 2nd part of the formula works fine as the lookup returns the appropriate P8 value from the tab in workbook 1 when it is not found in workbook2. However, when the value IS found in workbook2, it returns a value of FALSE, not the appropriate lookup value. Should I be using a different IS function? Any assistance would be greatly appreciated!! -- Thanks so much! |
ISNA with VLOOKUP against 2 locations
The logic of your formula is "If there is an error looking in book2,
then look in book1". You don't say to look in book 2 if there is no error, so you might like to amend your formula to: =IF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S, 17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F, 5,FALSE),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)) However, this does not check for the item being in book1 - it assumes it is there, and if not the formula will return #N/A. A more generic approach would be: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not present",vlookup_2),vlookup_1) Looks in table_1 first and returns from there if present, otherwise looks in table_2 and returns from there if present. If absent from both, then the error message is returned. Hope this helps. Pete On Feb 4, 4:00*pm, steph wrote: I have a new problem I need help with. *I am trying to look up a value found in workbook #1. *I need to lookup the value in workbook #2 and, if it's not found there, then lookup the value in a different tab of workbook #1. *Here is what I have so far. equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P*8,'tabname in workbook1'!$A:$F,5,FALSE)) The 2nd part of the formula works fine as the lookup returns the appropriate P8 value from the tab in workbook 1 when it is not found in workbook2. * However, when the value IS found in workbook2, it returns a value of FALSE, not the appropriate lookup value. Should I be using a different IS function? *Any assistance would be greatly appreciated!! -- Thanks so much! |
ISNA with VLOOKUP against 2 locations
Yes, this worked perfectly--thanks very much!!
-- Thanks so much! "T. Valko" wrote: Try this: =lIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,5,0),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)) -- Biff Microsoft Excel MVP "steph" wrote in message ... I have a new problem I need help with. I am trying to look up a value found in workbook #1. I need to lookup the value in workbook #2 and, if it's not found there, then lookup the value in a different tab of workbook #1. Here is what I have so far. equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,5,FALSE)) The 2nd part of the formula works fine as the lookup returns the appropriate P8 value from the tab in workbook 1 when it is not found in workbook2. However, when the value IS found in workbook2, it returns a value of FALSE, not the appropriate lookup value. Should I be using a different IS function? Any assistance would be greatly appreciated!! -- Thanks so much! |
ISNA with VLOOKUP against 2 locations
Pete, this worked perfectly. I couldn't get your 2nd suggestion to work but
your 1st suggestion matched T. Valko's (I'm OK with getting #N/A so I can manually add those values to one of my workbooks). The only difference between your suggestiong and T's was T used 0 where you used FALSE. Can you tell me the difference? -- Thanks so much! "Pete_UK" wrote: The logic of your formula is "If there is an error looking in book2, then look in book1". You don't say to look in book 2 if there is no error, so you might like to amend your formula to: =IF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S, 17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F, 5,FALSE),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)) However, this does not check for the item being in book1 - it assumes it is there, and if not the formula will return #N/A. A more generic approach would be: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not present",vlookup_2),vlookup_1) Looks in table_1 first and returns from there if present, otherwise looks in table_2 and returns from there if present. If absent from both, then the error message is returned. Hope this helps. Pete On Feb 4, 4:00 pm, steph wrote: I have a new problem I need help with. I am trying to look up a value found in workbook #1. I need to lookup the value in workbook #2 and, if it's not found there, then lookup the value in a different tab of workbook #1. Here is what I have so far. equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(PĀ*8,'tabname in workbook1'!$A:$F,5,FALSE)) The 2nd part of the formula works fine as the lookup returns the appropriate P8 value from the tab in workbook 1 when it is not found in workbook2. However, when the value IS found in workbook2, it returns a value of FALSE, not the appropriate lookup value. Should I be using a different IS function? Any assistance would be greatly appreciated!! -- Thanks so much! |
ISNA with VLOOKUP against 2 locations
They are both the same - I usually use 0 rather than FALSE as it is
less typing, but in this case I just copied your formula as you had presented it. Thanks for feeding back. Pete On Feb 4, 5:47*pm, steph wrote: Pete, this worked perfectly. *I couldn't get your 2nd suggestion to work but your 1st suggestion matched T. Valko's (I'm OK with getting #N/A so I can manually add those values to one of my workbooks). *The only difference between your suggestiong and T's was T used 0 where you used FALSE. *Can you tell me the difference? -- Thanks so much! "Pete_UK" wrote: The logic of your formula is "If there is an error looking in book2, then look in book1". You don't say to look in book 2 if there is no error, so you might like to amend your formula to: =IF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S, 17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F, 5,FALSE),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)) However, this does not check for the item being in book1 - it assumes it is there, and if not the formula will return #N/A. A more generic approach would be: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not present",vlookup_2),vlookup_1) Looks in table_1 first and returns from there if present, otherwise looks in table_2 and returns from there if present. If absent from both, then the error message is returned. Hope this helps. Pete On Feb 4, 4:00 pm, steph wrote: I have a new problem I need help with. *I am trying to look up a value found in workbook #1. *I need to lookup the value in workbook #2 and, if it's not found there, then lookup the value in a different tab of workbook #1. *Here is what I have so far. equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P**8,'tabname in workbook1'!$A:$F,5,FALSE)) The 2nd part of the formula works fine as the lookup returns the appropriate P8 value from the tab in workbook 1 when it is not found in workbook2. * However, when the value IS found in workbook2, it returns a value of FALSE, not the appropriate lookup value. Should I be using a different IS function? *Any assistance would be greatly appreciated!! -- Thanks so much!- Hide quoted text - - Show quoted text - |
ISNA with VLOOKUP against 2 locations
T used 0 where you used FALSE.
Can you tell me the difference? Saves a few keystrokes! 0 = FALSE 1 = TRUE -- Biff Microsoft Excel MVP "steph" wrote in message ... Pete, this worked perfectly. I couldn't get your 2nd suggestion to work but your 1st suggestion matched T. Valko's (I'm OK with getting #N/A so I can manually add those values to one of my workbooks). The only difference between your suggestiong and T's was T used 0 where you used FALSE. Can you tell me the difference? -- Thanks so much! "Pete_UK" wrote: The logic of your formula is "If there is an error looking in book2, then look in book1". You don't say to look in book 2 if there is no error, so you might like to amend your formula to: =IF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S, 17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F, 5,FALSE),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)) However, this does not check for the item being in book1 - it assumes it is there, and if not the formula will return #N/A. A more generic approach would be: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not present",vlookup_2),vlookup_1) Looks in table_1 first and returns from there if present, otherwise looks in table_2 and returns from there if present. If absent from both, then the error message is returned. Hope this helps. Pete On Feb 4, 4:00 pm, steph wrote: I have a new problem I need help with. I am trying to look up a value found in workbook #1. I need to lookup the value in workbook #2 and, if it's not found there, then lookup the value in a different tab of workbook #1. Here is what I have so far. equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P*8,'tabname in workbook1'!$A:$F,5,FALSE)) The 2nd part of the formula works fine as the lookup returns the appropriate P8 value from the tab in workbook 1 when it is not found in workbook2. However, when the value IS found in workbook2, it returns a value of FALSE, not the appropriate lookup value. Should I be using a different IS function? Any assistance would be greatly appreciated!! -- Thanks so much! |
ISNA with VLOOKUP against 2 locations
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "steph" wrote in message ... Yes, this worked perfectly--thanks very much!! -- Thanks so much! "T. Valko" wrote: Try this: =lIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,5,0),VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,0)) -- Biff Microsoft Excel MVP "steph" wrote in message ... I have a new problem I need help with. I am trying to look up a value found in workbook #1. I need to lookup the value in workbook #2 and, if it's not found there, then lookup the value in a different tab of workbook #1. Here is what I have so far. equalIF(ISNA(VLOOKUP(P8,'[workbook2.xls]tabname'!$C:$S,17,FALSE)),VLOOKUP(P8,'tabname in workbook1'!$A:$F,5,FALSE)) The 2nd part of the formula works fine as the lookup returns the appropriate P8 value from the tab in workbook 1 when it is not found in workbook2. However, when the value IS found in workbook2, it returns a value of FALSE, not the appropriate lookup value. Should I be using a different IS function? Any assistance would be greatly appreciated!! -- Thanks so much! |
| All times are GMT +1. The time now is 12:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com