Opposite of ISNA Function

[jeffgibson55]

I often need a formula to not return an error value (e.g., "#NA") because it throws off my formulas and pivot tables, so I often find myself throwing an "IF(ISNA" into my formulas to return a custom value or to do another formula in the case of an error otherwise being returned.

But when I have a lot of conditions, this "ISNA" logic gets long and complex so I'd rather use the inverse: "IFNotISNA" a/k/a "IfTrue". Does such an inverse to ISNA exist?

Example: I want to look something up, but if an error is returned I want to look elsewhere - below is how I write these things now (using "IF(ISNA"'s). This is fine for a few conditionals, but when I want to add a bunch of error-conditional stuff using the "IF(ISNA" the formula gets really clunky and hard to follow.

If(ISNA(Vlookup(Sheet1!A1,Sheet2!X1:Z99,2,false)),Vlookup(Sheet1!A1,Sheet3!X1:Z99,2,false),Vlookup(Sheet1!A1,Sheet2!X1:Z99,2,false)

Thanks!
Jeff

 

[Andrew Poulsom]

You can use:

=IF(NOT(ISNA(YourFormula)),YourFormula,"")

 

[Firefly2012]

Hi

Within an IF if you want a True returned for a non-ISNA result you could use the following construct:

=IF(1-ISNA(term),Value If Not ISNA, Value if ISNA)

This works because if ISNA() returns True then 1-True = 0 (ie False argument returned), else 1-False=0 (True argument returned).

 

[jeffgibson55]

Thanks so much for the quick help guys! The quick solutions found on this forum never ceases to surprise me!

"If(Not" opens up a lot of doors for me!

Thanks!
Jeff

 

[T. Valko]

I often need a formula to not return an error value (e.g., "#NA") because it throws off my formulas and pivot tables, so I often find myself throwing an "IF(ISNA" into my formulas to return a custom value or to do another formula in the case of an error otherwise being returned.

But when I have a lot of conditions, this "ISNA" logic gets long and complex so I'd rather use the inverse: "IFNotISNA" a/k/a "IfTrue". Does such an inverse to ISNA exist?

Example: I want to look something up, but if an error is returned I want to look elsewhere - below is how I write these things now (using "IF(ISNA"'s). This is fine for a few conditionals, but when I want to add a bunch of error-conditional stuff using the "IF(ISNA" the formula gets really clunky and hard to follow.

If(ISNA(Vlookup(Sheet1!A1,Sheet2!X1:Z99,2,false)),Vlookup(Sheet1!A1,Sheet3!X1:Z99,2,false),Vlookup(Sheet1!A1,Sheet2!X1:Z99,2,false)

Thanks!
Jeff

Another way...

If your lookup formula returns TEXT:

=IF(ISTEXT(your_formula)...

If your lookup formula returns NUMBERS:

=IF(COUNT(your_formula)...

If your lookup formula could return either text or numbers:

=IF(NOT(ISNA(your_formula))...