Adding an Error to a formula for Excel 2003

[Cra5h]

Hi,

Hoping someone can help with a fomula concept that i need.

I have used Match and Index to return a value of a cell in a data range as follows :

=INDEX(DATA!$A$1:$O$1793,MATCH($A$10,DATA!$A$1:$A$1793,0),MATCH($B$10,DATA!$A$1:$O$1,0))

This is used to match a name that i key into cell A10 in the range selected in the Data Sheet in Column A and a reference that i key into cell B10 and locates a response. My problem is that some weeks certain items aren't in the DATA and instead of it returning '#N/A' i need it to return zero.

I have managed to this this in Excel 2007 by just wrapping the whole formula with an IFERROR (). This works ok but most of my colleagues are using excel 2003 and IFERROR doesn't work.

So i have tried to use an =IF(ISERROR fomula (below) but 1) Is it right?? and 2) Can it be done easier??

=IF(ISERROR(INDEX(DATA!$A$1:$O$1793,MATCH($A$10,DATA!$A$1:$A$1793,0),MATCH($B$10,DATA!$A$1:$O$1,0))),0,INDEX(DATA!$A$1:$O$1793,MATCH($A$10,DATA!$A$1:$A$1793,0),MATCH($B$10,DATA!$A$1:$O$1,0)))

Would be grateful for any help...

[Ace_XL]

1) Is it right??

Yep its fine

and 2) Can it be done easier??

Unfortunately not

[Tony Valko]

Try this...

=IF(COUNT(MATCH($A$10,DATA!$A$1:$A$1793,0)+MATCH($B$10,DATA!$A$1:$O$1,0)),VLOOKUP($A$10,DATA!$A$1:$O$1793,MATCH($B$10,DATA!$A$1:$O$1,0),0),0)

[Cra5h]

Thanks Tony,

That seems to work fine on mine, but will it work with excel 2003?

Cheers

[Tony Valko]

Yes, it will work in any version of Excel.