Replace #N/A with blank in array formula

[redrand]

Hi

I have a formula I have used to match data in two columns and return the value of the third column, however this formula shows #N/A if not results or matches occur.

below is the formula I am using
=INDEX('Contract Data'!F$3:F$144,MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0))

I have tried using the =IF(ISERROR and the =IF(ISERR for example

=IF(ISERROR(INDEX('Contract Data'!F$3:F$144,MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0))))

However, excel 2003 states there is an error with the formula and no idea where.

All I want to do is if no match is found or no result available, show the cell as blank.

Hope someone can help with this, thanks

 

[Richard Schollar]

Hi

You should be able to do that with:

=IF(ISNA(MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0)),"",INDEX('Contract Data'!F$3:F$144,MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0)))

 

[zakasnak]

Hi

=IF(ISERROR(INDEX('Contract Data'!F$3:F$144,MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0) )))

Don't you need to finish the IF statement?

=IF(ISERROR((INDEX('Contract Data'!F$3:F$144,MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0)))),0,(INDEX('Contract Data'!F$3:F$144,MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0))))

 

[jlatendre]

Hey, your missing what to do if it is true or false, here it is.

=IF(ISERROR(INDEX('Contract Data'!F$3:F$144,MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0)))," ",INDEX('Contract Data'!F$3:F$144,MATCH(Summary!$C$1&$D18,'Contract Data'!$A$3:$A$145&'Contract Data'!$B$3:$B$145,0)))
Jesse