Streamlining nested IF/ ISNA

[boatbabe]

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]

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

=IFERROR(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)

[boatbabe]

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

[Andrew-R]

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]

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

[Andrew-R]

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:

=IF(COUNTA(INDIRECT("'" & A4 & "'!A1:A1"))<2,IFERROR(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),"")

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