#Value! error with IndexMatch formula

[Todd R K]

Good morning.

I am using an index match formula to return the volume of seven different activities of a list of companies. Some of the activities may be blank for a particular company. When this occurs, I get a #value! error. I have tried using the IF(ISNA function and this hasn't solved the problem.

All I am looking for is a zero for the activity if a company has no volume for that particular activity for the month I am reviewing.

=IF(ISNA(INDEX('2004 deposits'!$A$2:$I$1500,MATCH($A10,'2004 deposits'!$A$2:$A$1500,0),4)/2000),0,INDEX('2004 deposits'!$A$2:$I$1500,MATCH($A10,'2004 deposits'!$A$2:$A$1500,0),4)/2000)

Any help would be greatly appreciated.

Thank you

Todd

 

[macleanb]

isna is strictly for NA as far as I know - have you tried using iserror instead...

 

[Todd R K]

Ben

Thank you for the quick reply. I have not tried the iserror yet. I will give it a try and see what happens.

Thank you

Todd

 

[Aladin Akyurek]

Good morning.

I am using an index match formula to return the volume of seven different activities of a list of companies. Some of the activities may be blank for a particular company. When this occurs, I get a #value! error. I have tried using the IF(ISNA function and this hasn't solved the problem.

All I am looking for is a zero for the activity if a company has no volume for that particular activity for the month I am reviewing.

=IF(ISNA(INDEX('2004 deposits'!$A$2:$I$1500,MATCH($A10,'2004 deposits'!$A$2:$A$1500,0),4)/2000),0,INDEX('2004 deposits'!$A$2:$I$1500,MATCH($A10,'2004 deposits'!$A$2:$A$1500,0),4)/2000)

Any help would be greatly appreciated.

Thank you

Todd

=IF(ISNUMBER(MATCH($A10,'2004 deposits'!$A$2:$A$1500,0)),INDEX('2004 deposits'!$A$2:$I$1500,MATCH($A10,'2004 deposits'!$A$2:$A$1500,0),4)/2000),0)

You'll still get a #VALUE error if INDEX returns a text value that you try to divide by a number.

 

[Todd R K]

Aladin

Thank you. The problem was solved once I used the ISERROR function.

Todd

 

[Aladin Akyurek]

Aladin

Thank you. The problem was solved once I used the ISERROR function.

Todd

That conceals any type of error... That means: It's not a good solution. Can INDEX return text values then?

 

[Todd R K]

Aladin

Thank you for the heads up on that issue. All of the data that I receive is numerical so I'm not too concerned about other types of errors. My only problem is if a cell is blank. The data that I receive is always in the same format (i.e. an Access query of the database).

Thanks again.

Todd