[Solved] Formulas: handling errors with vlookup

  • Dear all,

    I am using vlookup to search an incomplete list so as to return a corresponding value.

    If the entry I am searching for is not there, Excel returns #N/A.

    I would like to write a formula that goes something like if(vlookup search is successful, return value, "not found").

    I've tried IsErr, but this returns an error if you pass it a non-error value, so I can't then return the successful value.

    Hopefully the attachment will make more sense! When looking up the value 3 in the database, I would like to be able to say "not found", or use another function.

    Thanks in advance for your help,


  • [quote=IF(ISNA(VLOOKUP(E2,A$2:B$4,2,FALSE)),"Not Found",VLOOKUP(E2,A$2:B$4,2,FALSE))

    The long error trapping code required in Excel has always bugged me, especially having to write the vlookup twice as in the example above.

    That's why I created & use this UDF function:

    Place this code into a VBE module:

    Function ErrFix(formula, result)
    If IsError(formula) Then
    ErrFix = result
    ErrFix = formula
    End If
    End Function

    ..and on the spreadsheet use this formula:
    =ErrFix(formula, error result)

    =ErrFix(VLOOKUP(F5,C2:D12,2,FALSE),"Not found")

    It can also be used to trap #div/0 errors(returns a 0 or blank if e3 is 0.)

  • Yes, asap-utilities is great, I'd recommend it also, but it is an add-in and I am certain that none of my clients have this installed. It makes my life less complicated to put relevant UDFs into a spreadsheet module instead of asking clients to download A-U.

Participate now!

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