OR AND VLOOKUP

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.
 
E

Eduardo

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

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

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

Tony7659

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

Tony.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top