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.

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.

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.

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.

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.

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.