Custom Text For Vlookup When Value Not Found

  • Goodness that keyword title script is annoying!


    It wouldn't let my type the word vlookup, how is that superfluous?


    Anyways,


    I have a vlookup that returns #N/A and I want to do conditional formatting on all these values but it won't work.


    Here is the formula I'm using


    =IF(ISBLANK(A7),"",IF(VLOOKUP(A7,Sheet1!$A$1:$A$828,1,FALSE)=A7,"Match Found","Missing Account"))


    I'm not sure if I just did it wrong, but it always returns either Match Found or #N/A. If I change the code to


    =IF(ISBLANK(A7),"",IF(ISNA(VLOOKUP(A7,Sheet1!$A$1:$A$828,1,FALSE))=A7,"Match Found","Missing Account"))


    everything turns to "Missing Account" and it never has Match Found, so that is not the answer either. Any suggestions?

  • Re: Conditional Formatting For N/a Fields


    I would go for using the iserror formula...


    =IF(ISBLANK(A7),"",IF(ISERROR(VLOOKUP(A7,Sheet1!$A$1:$A$828,1,False)),"Missing Account","Match Found"))


    Cheers,
    Ian



    ADDED BY ADMIN
    =VLOOKUP(A7,Sheet1!$A$1:$A$828,1,False)
    In 1 column (eg "B") and;
    =IF(ISNA(B1),"Missing Account","Match Found")
    Hide column B

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!