[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,


    awoolley.

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


    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
    Else
    ErrFix = formula
    End If
    End Function


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


    ie:
    =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.)
    =ErrFix(E2/E3,0)
    =ErrFix(E2/E3,"")

    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!

Register Yourself Login