# #Value! error with IndexMatch formula

#### Todd R K

##### Board Regular
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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
isna is strictly for NA as far as I know - have you tried using iserror instead...

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

Todd R K said:
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.

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

Todd

Todd R K said:

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?

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

Replies
5
Views
265
Replies
4
Views
332
Replies
5
Views
248
Replies
1
Views
119
Replies
3
Views
305

1,203,047
Messages
6,053,197
Members
444,645
Latest member
mee siam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back