Nest an IFERROR() command in a general IF() formula in Excel

Posted on 2014-08-15
Last Modified: 2014-08-29

I think this is a question about how to nest an IFERROR() command in a general IF() formula. However, it may actually be more a question about the VLOOKUP() formula. I'm not sure.

Syntax (for my own reference):
        IFERROR(value, value_if_error)
        VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Suppose you have a spreadsheet with the following:

        Some range (with 2 columns) named:  Table
        Column A:  text entries
        Column B:  VLOOKUP() formula

where the Column B formula is:


The three possible scenarios are:

1) If the "lookup_value" from A1 exists in Table and the corresponding 2nd-column cell has a value,
        the formula will display that value.

2) If the "lookup_value" from A1 exists in Table and the corresponding 2nd-column cell is blank,
        the formula will display 0.

3) If the "lookup_value" from A1 does not exist in Table,
        the formula will display #N/A.

Now, in place of the above, what formula would display a backtick ("`") for both scenarios 2 and 3?

It seems like it should be something like this:


Open in new window

but I know that the IFERROR() part of the above is wrong.

What is the proper way to write it?

Question by:WeThotUWasAToad
    LVL 27

    Assisted Solution

    by:Glenn Ray
    To return a grave accent (aka "backtick" ... `) character if a VLOOKUP would return 0 or #N/A!, write the formula this way:

    IFERROR really doesn't work in this instance since it only allows two possible results - either a correct lookup or the replacement value for the error.  Since 0 is a valid result, it can't be trapped.  It's just easier to use the ISNA test and nested IF statements.

    LVL 50

    Accepted Solution

    It's possible to force a zero result from the VLOOKUP to give an error, e.g. by dividing 1 by the VLOOKUP, and then you could use this version


    That only works if zero is only ever returned as a result of a blank in the return column - if zero can be a valid result (not from a blank) and you want zero returned in that case then I would make a very small adjustment to Glenn's suggestion, i.e. check for "" rather than zero with the second IF like this:


    That may return zero if that's the actual value in column 2

    ......or if you never want a zero result and the other VLOOKUP results are numeric you could use this:


    Note: this might not be the sort of formula you want to use in a workbook that others need to use and understand, due to it's lack of transparency

    regards, barry
    LVL 27

    Assisted Solution

    by:Glenn Ray
    I knew Barry would have some ingenous ways to approach this, but I do need to point out that the first formula:

    is only applicable then the value to be returned from the VLOOKUP is numeric;
    otherwise, the result always evaluates to "`".

    Barry's second formula is spot-on and a necessary change expecially if you're dealing with numeric data and have the possibility of actual zero values in the source data.

    And, dangme, that last formula blew my mind.  Pretty cool.

    LVL 50

    Expert Comment

    by:barry houdini
    Hello Glenn,

    Yes, you're right, my mistake - I suppose the first formula can be disregarded - as it only works for numeric data you can use the third one instead

    regards, barry
    LVL 85

    Assisted Solution

    by:Rory Archibald
    FWIW, you can use IFERROR simply:


    If the returned values will always be text (other than blank cells), you can shorten it to:

    LVL 27

    Expert Comment

    by:Glenn Ray
    Rory, another good solution!  One minor correction on your second formula:
    =IFERROR(VLOOKUP(A4,Table,2,0) & "","`")  (remove 2nd close paranthesis)
    But as you said, that formula will not return a grave accent if there is a blank value in the lookup return column.

    This demonstrates one of the neat things about both Excel and EE:  there is almost always more than one method to finding a solution.


    Author Closing Comment

    Super suggestions! Thanks

    Ditto to Glenn's comment:
    "This demonstrates one of the neat things about both Excel and EE:  there is almost always more than one method to finding a solution."

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now