Error trapping

[Keith]

I am using the following array formula which entails excel doing the calculation twice. I have tried using countif in the match part but can only get it to work for only one of the conditions. Can anyone help please?

=If(ISERROR(INDEX(J6:J11,MATCH(1,IF(H6:H11=B6,IF(I6:I11=C6,1),0),FALSE))),"",INDEX(J6:J11,MATCH(1,IF(H6:H11=B6,IF(I6:I11=C6,1),0),FALSE)))

Keith

 

[Brianwarnock]

I'm not sure what you are asking, is it that you donot like the idea of Excel having to do the calculation twice?, this is not unusual.

Brian

 

[Keith]

I saw on another forum the use of CountIf to prevent a #N/A error. I have played arround for some time trying to adapt my formula to no avail, However using IfError works fine so i'll leave it at that.
Thanks Brian
Keith

 

[Brianwarnock]

IMHO getting #N/A when there is no match qould be a fair result as it stands for Nobody Available , but then I don't know what your next step is' plus you can always test for th#N/A later, say the formula was in B16 then If(iserror(b16),"Nobody Available",b16) or whatever.


Brian

 

[Keith]

I've just come across this old post of mine and realised that although I had worked out a solution to the error trapping problem I hadn't posted it.

{=IF(ISERROR(INDEX(Cover,MATCH(1,(LeaveDate=E2)*(Name=F2),0)))=FALSE,INDEX(Cover,MATCH(1,(LeaveDate=E2)*(Name=F2),0)),"")}