# #N/A logic

#### garethgtt

##### New Member
I have the following formula:

=INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,(E\$5=Sheet4!\$A\$1:\$A\$2216)*(B7=Sheet4!\$B\$1:\$B\$216),0))

now when it does not find a match it displays #N/A -- how can i instead get it to show either nothing or a 0

?
Many thanks

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Randombard

##### Active Member
You need to add an "If" statement

=IF(ISERROR(INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,(E\$5=Sheet4!\$A\$1:\$A\$2216)*(B7=Sheet4!\$B\$1:\$B\$216),0))),0,INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,(E\$5=Sheet4!\$A\$1:\$A\$2216)*(B7=Sheet4!\$B\$1:\$B\$216),0)))

something like that

#### banjoflanjo

##### New Member
Try this:

=if(isna(INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,(E\$5=Sheet4!\$A\$1:\$A\$2216)*(B7=Sheet4!\$B\$1:\$B\$216),0))),"",INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,(E\$5=Sheet4!\$A\$1:\$A\$2216)*(B7=Sheet4!\$B\$1:\$B\$216),0)))

for leaving blank.

or,

=if(isna(INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,(E\$5=Sheet4!\$A\$1:\$A\$2216)*(B7=Sheet4!\$B\$1:\$B\$216),0))),0,INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,(E\$5=Sheet4!\$A\$1:\$A\$2216)*(B7=Sheet4!\$B\$1:\$B\$216),0)))

for entering a 0.

Hope that helps,

Banjoflanjo

##### MrExcel MVP
I have the following formula:

=INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,(E\$5=Sheet4!\$A\$1:\$A\$2216)*(B7=Sheet4!\$B\$1:\$B\$216),0))

now when it does not find a match it displays #N/A -- how can i instead get it to show either nothing or a 0

?
Many thanks

If the formula is supposed to return a number when successful...

Control+shift+enter, not just enter:

Code:
``````=LOOKUP(9.99999999999999E+307,
CHOOSE({1,2},0,INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,
IF(E\$5=Sheet4!\$A\$1:\$A\$2216,IF(B7=Sheet4!\$B\$1:\$B\$216,1)),0))))``````

Note. If you are on Excel 2007 or beyond, have a look at the IFERROR function.

#### garethgtt

##### New Member
If the formula is supposed to return a number when successful...

Control+shift+enter, not just enter:

Code:
``````=LOOKUP(9.99999999999999E+307,
CHOOSE({1,2},0,INDEX(Sheet4!\$C\$1:\$C\$205,MATCH(1,
IF(E\$5=Sheet4!\$A\$1:\$A\$2216,IF(B7=Sheet4!\$B\$1:\$B\$216,1)),0))))``````

Note. If you are on Excel 2007 or beyond, have a look at the IFERROR function.

Thanks very much!

Replies
1
Views
166
Replies
18
Views
489
Replies
0
Views
131
Replies
11
Views
905
Replies
18
Views
754

1,191,191
Messages
5,985,215
Members
439,947
Latest member
fabiannic

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