I have given up on trying to resolve this. I am trying to use the following formula as shown below (I have pulled out just the MATCH function as well). There is no reason why I should be receiving a #N/A error. I have triple checked that the value does exist and the MATCH conditions should all return true. When I evaluate the formula, the first condition returns TRUE and FALSE which is subsequently converted into 0 and 1 as appropriate. The second condition returns TRUE and FALSE but is not converted to 0 and 1 but #N/A at every instance. I don't understand why and most of the rows that contain this formula work just fine.

=INDEX(Sum_basin!$A$2:$F$2534,MATCH(1,(Sum_basin!$A$2:$A$2534=B11004)*(Sum_state!$B$2:$B$1061=Results_FY13!H11004)*(Sum_state!$C$2:$C$1061=Results_FY13!I11004),0),6)

MATCH(1,(Sum_basin!$A$2:$A$2534=B11004)*(Sum_state!$B$2:$B$1061=Results_FY13!H11004)*(Sum_state!$C$2:$C$1061=Results_FY13!I11004),0)