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?


    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"))


    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!