When ISNA doesn't work....

[collywobbles]

Hi,

I have an IF statement to returning "Yes" "No" possible list, using a VLOOKUP. However I also get the #N/A's included which I want to eliminate. How can I go about this when ISNA doesn't work in this situation.

The formula is this:

=IF(VLOOKUP(J12,'\\ogg\Shared Areas\LLU Programme of Works\LLU Rollout PM\[X Factor.xls]Handover Register'!$C:$I,7,FALSE)="Pass with snags","Yes","No")

Regards,

C

 

[lozzablake]

Why does ISNA not work?

 

[markoakes]

You might try ISERROR, something like;

=IF(ISERROR(VLOOKUP(J12,'\\ogg\Shared Areas\LLU Programme of Works\LLU Rollout PM\[X Factor.xls]Handover Register'!$C:$I,7,FALSE)="Pass with snags","Yes","No"),””, IF(VLOOKUP(J12,'\\ogg\Shared Areas\LLU Programme of Works\LLU Rollout PM\[X Factor.xls]Handover Register'!$C:$I,7,FALSE)="Pass with snags","Yes","No")

 

[Domenic]

Try...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",IF(VLOOKUP(J12,'\\ogg\Shared Areas\LLU Programme of Works\LLU Rollout PM\[X Factor.xls]Handover Register'!$C:$I,7,FALSE)="Pass with snags","Yes","No")))

Hope this helps!