Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
OR AND VLOOKUP
I am using VLOOKUP to find a number in cell $A26 that has be searched in
different tabs and columns. To accomplish this, I am combining VLOOKUP and OR without results (#N/A). If I use IFs alone, I would exceed the nested limit. =IF(OR(VLOOKUP($A26,SYS1!$A:$A,1,FALSE),VLOOKUP($A 26,SYS1!$R:$R,1,FALSE)),"NY1",IF(OR(VLOOKUP($A26,S YS2!$A:$A,1,FALSE),VLOOKUP($A26,SYS2!$R:$R,1,FALSE )),0)) Any ideas? Tony. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
OR AND VLOOKUP
Hi,
could you please give an example, which value is in cell A26, is the sum of ??. Thank you "Tony7659" wrote: I am using VLOOKUP to find a number in cell $A26 that has be searched in different tabs and columns. To accomplish this, I am combining VLOOKUP and OR without results (#N/A). If I use IFs alone, I would exceed the nested limit. =IF(OR(VLOOKUP($A26,SYS1!$A:$A,1,FALSE),VLOOKUP($A 26,SYS1!$R:$R,1,FALSE)),"NY1",IF(OR(VLOOKUP($A26,S YS2!$A:$A,1,FALSE),VLOOKUP($A26,SYS2!$R:$R,1,FALSE )),0)) Any ideas? Tony. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
OR AND VLOOKUP
VLOOKUP returns #N/A if lookup value is not found... so you have to wrap it
in ISNA like this =IF(ISNA(Vlookup(...),"",ISNA(Vlookup(...)) it will return an empty string if lookup value is not found... Write your vlookups like the example above and then concatenate... =IF() & IF() & IF()... it will work since only one will return a value... if return values are numbers then use + instead of & "Tony7659" wrote: I am using VLOOKUP to find a number in cell $A26 that has be searched in different tabs and columns. To accomplish this, I am combining VLOOKUP and OR without results (#N/A). If I use IFs alone, I would exceed the nested limit. =IF(OR(VLOOKUP($A26,SYS1!$A:$A,1,FALSE),VLOOKUP($A 26,SYS1!$R:$R,1,FALSE)),"NY1",IF(OR(VLOOKUP($A26,S YS2!$A:$A,1,FALSE),VLOOKUP($A26,SYS2!$R:$R,1,FALSE )),0)) Any ideas? Tony. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
OR AND VLOOKUP
Not sure what you're trying to do but from what you posted you can replace
the VLOOKUPs with COUNTIFs: =IF(COUNTIF(SYS1!$A:$A,$A26)+COUNTIF(SYS1!$R:$R,$A 26),"NY1",IF(COUNTIF(SYS2!$A:$A,$A26)+COUNTIF(SYS2 !$R:$R,$A26),0,"")) -- Biff Microsoft Excel MVP "Tony7659" wrote in message ... I am using VLOOKUP to find a number in cell $A26 that has be searched in different tabs and columns. To accomplish this, I am combining VLOOKUP and OR without results (#N/A). If I use IFs alone, I would exceed the nested limit. =IF(OR(VLOOKUP($A26,SYS1!$A:$A,1,FALSE),VLOOKUP($A 26,SYS1!$R:$R,1,FALSE)),"NY1",IF(OR(VLOOKUP($A26,S YS2!$A:$A,1,FALSE),VLOOKUP($A26,SYS2!$R:$R,1,FALSE )),0)) Any ideas? Tony. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
OR AND VLOOKUP
Perfect! It works great. Sorry for the delay, I could not find my message
yesterday to follow up. Tony. "T. Valko" wrote: Not sure what you're trying to do but from what you posted you can replace the VLOOKUPs with COUNTIFs: =IF(COUNTIF(SYS1!$A:$A,$A26)+COUNTIF(SYS1!$R:$R,$A 26),"NY1",IF(COUNTIF(SYS2!$A:$A,$A26)+COUNTIF(SYS2 !$R:$R,$A26),0,"")) -- Biff Microsoft Excel MVP "Tony7659" wrote in message ... I am using VLOOKUP to find a number in cell $A26 that has be searched in different tabs and columns. To accomplish this, I am combining VLOOKUP and OR without results (#N/A). If I use IFs alone, I would exceed the nested limit. =IF(OR(VLOOKUP($A26,SYS1!$A:$A,1,FALSE),VLOOKUP($A 26,SYS1!$R:$R,1,FALSE)),"NY1",IF(OR(VLOOKUP($A26,S YS2!$A:$A,1,FALSE),VLOOKUP($A26,SYS2!$R:$R,1,FALSE )),0)) Any ideas? Tony. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
OR AND VLOOKUP
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tony7659" wrote in message ... Perfect! It works great. Sorry for the delay, I could not find my message yesterday to follow up. Tony. "T. Valko" wrote: Not sure what you're trying to do but from what you posted you can replace the VLOOKUPs with COUNTIFs: =IF(COUNTIF(SYS1!$A:$A,$A26)+COUNTIF(SYS1!$R:$R,$A 26),"NY1",IF(COUNTIF(SYS2!$A:$A,$A26)+COUNTIF(SYS2 !$R:$R,$A26),0,"")) -- Biff Microsoft Excel MVP "Tony7659" wrote in message ... I am using VLOOKUP to find a number in cell $A26 that has be searched in different tabs and columns. To accomplish this, I am combining VLOOKUP and OR without results (#N/A). If I use IFs alone, I would exceed the nested limit. =IF(OR(VLOOKUP($A26,SYS1!$A:$A,1,FALSE),VLOOKUP($A 26,SYS1!$R:$R,1,FALSE)),"NY1",IF(OR(VLOOKUP($A26,S YS2!$A:$A,1,FALSE),VLOOKUP($A26,SYS2!$R:$R,1,FALSE )),0)) Any ideas? Tony. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |