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

Hello,

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:

        =VLOOKUP(A1,Table,2,0)

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:

        =
        IF(VLOOKUP(A1,Table,2,0)<>"",VLOOKUP(A1,Table,2,0),
        IF(VLOOKUP(A1,Table,2,0)=0,"`",
        IFERROR(VLOOKUP(A1,Table,2,0),"`")))

Open in new window

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

What is the proper way to write it?

Thanks
WeThotUWasAToadAsked:
Who is Participating?
 
barry houdiniCommented:
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

=IF(ISERROR(1/VLOOKUP(A1,Table,2,0)),"`",VLOOKUP(A1,Table,2,0))

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:

=IF(ISNA(VLOOKUP(A1,Table,2,0)),"`",IF(VLOOKUP(A1,Table,2,0)="","`",VLOOKUP(A1,Table,2,0)))

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:

=IFERROR(1/(1/VLOOKUP(A1,Table,2,0)),"`")

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
0
 
Glenn RayExcel VBA DeveloperCommented:
To return a grave accent (aka "backtick" ... `) character if a VLOOKUP would return 0 or #N/A!, write the formula this way:
=IF(ISNA(VLOOKUP(A1,Table,2,FALSE)),"`",IF(VLOOKUP(A1,Table,2,0)=0,"`",VLOOKUP(A1,Table,2,0)))

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.

Regards,
-Glenn
0
 
Glenn RayExcel VBA DeveloperCommented:
I knew Barry would have some ingenous ways to approach this, but I do need to point out that the first formula:
=IF(ISERROR(1/VLOOKUP(A1,Table,2,0)),"`",VLOOKUP(A1,Table,2,0))

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.

-Glenn
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
barry houdiniCommented:
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
0
 
Rory ArchibaldCommented:
FWIW, you can use IFERROR simply:

=IFERROR(IF(VLOOKUP(A1,Table,2,0)="","`",VLOOKUP(A1,Table,2,0)),"`")

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

=IFERROR(VLOOKUP(A1,Table,2,0)&""),"`")
0
 
Glenn RayExcel VBA DeveloperCommented:
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.

-Glenn
0
 
WeThotUWasAToadAuthor Commented:
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."
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.