Ifnoterror

[sdruley]

With more recent versions of Excel the syntax of IFERROR is a very efficient shortcut over IF(ISERROR(.
In the case of NOT, OR and AND, I am wondering if IFNOTERROR, IFORERROR, IFANDERROR wouldn't be equally well suited to reduce the length of worksheet formulas and increase processing efficiency.
My questions are,
1) does this functionality or something similar exist in Excel?
2) would you agree on the noteworthiness of these hypothetical functions?

 

[Joe4]

Since this is more of a discussion-type question, I moved it to the "General Excel Discussion & Other Questions Forums" (leaving a link behind from the other forum for a week, so it can still also be seen from there).

As you might have guessed, those three hypothetical functions do not exist in Excel, nor do I know anything similar to them. I am not exactly sure what would be gained by something like IFNOTERROR. The way the IFERROR function works, the first argument is the expression you are evaluating (and what is returned if there is no error). The second argument is what to return in the event of an error in evaluating the first expression. So the case of an error or not is already incorporated to the formula, so I don't know what value having something like IFNOTERROR would add, unless you are envisioning something different than this.

If you are just trying to evaluate whether or not a statement returns an error, than you would want ISERROR in that case.

 

[shg]

For your IFANDERROR (or maybe IFORERROR), you can use nested IFERROR functions to return the first non-error value:

=iferror(expr1, iferror(expr2, iferror(expr3, "really bad")))

 

[sdruley]

Excellent feedback, gentlemen. shg, thank you for "nested" idea. I believe this helps me understand how to get to the next level of efficient coding.

Steve

 

[lchacho]

I had a similar need, that I figured out a workable answer for, by combining & nesting IFERROR and IF functions.

My need was to confirm whether a unique ID was present or not in several tables, and display that fact in corresponding columns on a master sheet. I.E. the column names would be "#exists in Table 1", "#exists in Table 2" etc.

I used the MATCH function to find the row it would be in, so if it did not exist=#NA. Boss wanted a Y/N approach so I needed to turn Non Errors into " Yes"

My Soultion:

=IFERROR(IF(MATCH(lookup_value, lookup_array, [match_type])>0, "Yes", ""), "No")

MATCH gives me a row number (always greater than 0) is it exists, if it does not exist it returns #NA

IF my match is greater that Zero, then it returns "Yes," solving my Non Error Problem. There can by no values less than 0 given the functionality of MATCH, so the only other values left are #NA, where no match exists, so these #NA would Carry Through.

IFERROR then does its job, and turns the errors into "No"


I decided to respond/post here because I googled the same question and this was the first thread I found, 2 years after the fact.

Sidenote: If anyone knows how to demonstrate the number of times a Foreign Key Unique ID appears in a table, it would be much appreciated. The tables I needed to check were put together in such a way where the Unique ID I needed to match appeared multiple times.

Disclaimer: I am not the best with excel or coding, so I may have juryrigged a solution to a problem for which their is already an easier formula.