Returning a 1 if the rules are met

For "Example 1"

"Helper" in Column X

in X2

=IF(N2="SG",MATCH(-U2,$U$2:$U$1000,0),"")

copy down

in W2

=IF(X2<>"",IF(AND(INDEX($N$2:$N$1000,X2)="DQ",INDEX($J$2:$J$1000,X2)=J2),1,""),"")

Can you see if this correct: meanwhile I will look at "Example 2

For "example 2"

In X2

=IF(N2="SL",IFERROR(MATCH(-U2,$U$2:$U$1000,0),""),"")

Copy down

in W2

=IF(X2<>"",IF(AND(INDEX($J$2:$J$1000,X2)=J2,INDEX($R$2:$R$1000,X2)=R2+1),1,""),"")

Example 1

Thanks for your reply

I got the columns wrong the SG and SL are in column M and the values are in column T(my bad)

I did put the formula in (just changed the column letters)

=IF(M2="SG",MATCH(-T2,$T$2:$T$1000,0),"")

&

=IF(X2<>"",IF(AND(INDEX($M$2:$M$1000,X2)="DQ",INDEX($J$2:$J$1000,X2)=J2),1,""),"")

In W2 it did return 1's but only for the debits

Example 2

It did return 1's but just for debits and only 7 of them

I can go into more detail if I am not making much sense

Not in your posted files. N and U

Try in "Example 1"

in W2

=IF(OR(M2="SG",M2="DQ"),MATCH(-T2,$T$2:$T$1000,0),"")

in V2

=IF(W2<>"",IF(OR(AND(INDEX($M$2:$M$1000,W2)="DQ",INDEX($J$2:$J$1000,W2)=J2),AND(INDEX($M$2:$M$1000,W2)="SG",INDEX($J$2:$J$1000,W2)=J2)),1,""),"")

in Example 2

in w2

=IF(OR(M2="SL",M2="SG"),IFERROR(MATCH(-T2,$T$2:$T$1000,0),""))

in V2

=IF(W2<>"",IF(OR(AND(INDEX($J$2:$J$1000,W2)=J2,INDEX($Q$2:$Q$1000,W2)=Q2+1),AND(INDEX($J$2:$J$1000,W2)=J2,INDEX($Q$2:$Q$1000,W2)=Q2-1)),1,""),"")

Ok I will give this a go

See the attached.

Column X shows the value corresponding to that in T: there is duplication because each entry shows its opposing entry i.e Credit shows Debit and the Debit will show the Credit.