OR AND VLOOKUP

  • Thread starter Thread starter Tony7659
  • Start date Start date
T

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.
 
Hi,
could you please give an example, which value is in cell A26, is the sum of
??. Thank you
 
Hi,
could you please give an example, which value is in cell A26, is the sum of
??. Thank you
 
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 &
 
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 &
 
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,""))
 
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,""))
 
Perfect! It works great. Sorry for the delay, I could not find my message
yesterday to follow up.

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

Tony.
 
Back
Top