A B C
1 30101 30102 ok
2 30101 30103 ok
3 30101 30104 ok
4 30102 30101 ok
5 30203 30101 not ok
6 30104 30101 ok

please help me for Cross matching A with B Vice versa by EXCEL function.

I think you want something like this in C1:

=IF(ISNA(MATCH(A1,B:B,0)),"not ok","ok")

and then copy down. You could also put this in D1:

=IF(ISNA(MATCH(B1,A:A,0)),"not ok","ok")

and copy down to look for uniques in column B.

Hope this helps.

Pete

On Feb 18, 12:59*am, RNO <R...@discussions.microsoft.com> wrote:
> * * * * A * * * B * * * * * *C
> 1 * * * 30101 * 30102 * * ok
> 2 * * * 30101 * 30103 * * ok
> 3 * * * 30101 * 30104 * * ok
> 4 * * * 30102 * 30101 * * ok
> 5 * * * 30203 * 30101 * * not ok
> 6 * * * 30104 * 30101 * * ok
>
> please help me for Cross matching A with B Vice versa by EXCEL function.

=IF(COUNTIF($B$1:$B$6,A1),"ok","not ok")


"RNO" wrote:

> A B C
> 1 30101 30102 ok
> 2 30101 30103 ok
> 3 30101 30104 ok
> 4 30102 30101 ok
> 5 30203 30101 not ok
> 6 30104 30101 ok
>
> please help me for Cross matching A with B Vice versa by EXCEL function.