OR AND VLOOKUP

[Tony7659]

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($A26,SYS1!$R:$R,1,FALSE)),"NY1",IF(OR(VLOOKUP($A26,SYS2!$A:$A,1,FALSE),VLOOKUP($A26,SYS2!$R:$R,1,FALSE)),0))

Any ideas?
Tony.

 

[Eduardo]

Hi,
could you please give an example, which value is in cell A26, is the sum of
??. Thank you

 

[Eduardo]

Hi,
could you please give an example, which value is in cell A26, is the sum of
??. Thank you

 

[Sheeloo]

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 &

 

[Sheeloo]

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 &

 

[T. Valko]

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,$A26),"NY1",IF(COUNTIF(SYS2!$A:$A,$A26)+COUNTIF(SYS2!$R:$R,$A26),0,""))

 

[T. Valko]

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,$A26),"NY1",IF(COUNTIF(SYS2!$A:$A,$A26)+COUNTIF(SYS2!$R:$R,$A26),0,""))

 

[Tony7659]

Perfect! It works great. Sorry for the delay, I could not find my message
yesterday to follow up.

Tony.

 

[Tony7659]

Perfect! It works great. Sorry for the delay, I could not find my message
yesterday to follow up.

Tony.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

You're welcome. Thanks for the feedback!