Streamlining nested IF/ ISNA

**boatbabe** · 08-25-2011, 07:43 AM

Hi there I have a formula which returns #NA in several cells, is there a way to replace this with a zero without nesting an IF and ISNA and repeating the formula? It just feels a bit clunky... if anyone can streamline it I would be grateful.
My formula is:
=INDEX(INDIRECT("'"&A4&"'!$CW$112:$EA$163"),MATCH(B4,INDIRECT("'"&A4&"'!$b$112:$b$163"),0),MATCH($T$2,INDIRECT("'"&A4&"'!$Cw$7:$ea$7"),0))

and so to add the IF and ISNA it becomes:
=IF(ISNA(INDEX(INDIRECT("'"&A4&"'!$CW$112:$EA$163"),MATCH(B4,INDIRECT("'"&A4&"'!$b$112:$b$163"),0),MATCH($T$2,INDIRECT("'"&A4&"'!$Cw$7:$ea$7"),0))),0,INDEX(INDIRECT("'"&A4&"'!$CW$112:$EA$163"),MATCH(B4,INDIRECT("'"&A4&"'!$b$112:$b$163"),0),MATCH($T$2,INDIRECT("'"&A4&"'!$Cw$7:$ea$7"),0)))

which as you can see is a bit of a mouthful!!
Thanks

**Andrew-R** · 08-25-2011, 07:50 AM

If you're using Excel 2007 or later you can use the IFERROR function:

Please Login or Register  to view this content.

**boatbabe** · 08-25-2011, 08:10 AM

Thanks. Will this zero #REFs as well though? As these would be useful to spot.

**Andrew-R** · 08-25-2011, 09:24 AM

It will, unfortunately, but at the moment there's no IFNA function, so you either return one value for all errors or you have to work with the long-winded formula.

Edited to add: Or you could write an IFNA UDF, I suppose. Sooner you than me

**boatbabe** · 08-25-2011, 09:39 AM

I think it will take me less time to go with the long winded way - thanks!

**Andrew-R** · 08-25-2011, 10:26 AM

Looking at your formula the only way you could get a REF error is if the sheet named in cell A4 doesn't exist, so you could change your formula to:

Please Login or Register  to view this content.

The first COUNTA is just a random operation on the named sheet to see if it exist - if it doesn't you'll get a REF error, because that operation is outside the IFERROR statement.

Mind you, I'm not sure it's any quicker than the long-winded formula