Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-15
7
Medium Priority
?
751 Views
Last Modified: 2014-08-29
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
0
Comment
Question by:WeThotUWasAToad
7 Comments
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 600 total points
ID: 40264682
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 800 total points
ID: 40264887
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 600 total points
ID: 40265194
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50

Expert Comment

by:barry houdini
ID: 40265275
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 600 total points
ID: 40267449
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40268246
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
 

Author Closing Comment

by:WeThotUWasAToad
ID: 40294034
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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