Finding pairs of cells in two columns-CodePudding

I am trying to find pair of cells from Sheet2 and see if they exist as pair in Sheet1. Where in Sheet1 I have:

and in Sheet2 I have:

If they match, I want to return 1, and 0 if they don't.

I have tried things like:

=NOT(ISERROR(FIND(TEXTJOIN("|",FALSE,A1:B1),TEXTJOIN("|",FALSE,Sheet1!$A$1:$B$5))))

but this does not work properly. It returns TRUE even when it is FALSE.

I have also tried a different approach, but I cannot see they way to continue with the pair. This formula only matches one of the elements of the pair.

=IF(COUNTIF(Sheet1!$A$1:$B$5,A1),1,0)

CodePudding user response：

Perhaps you could try in this way, it works for me,

• Formula used in cell D1

=N(ISNUMBER(FIND(TEXTJOIN("|",,$A$8:$B$8),TEXTJOIN("|",,$A1:$B1))))

You can also use MAP() function with XMATCH()

• Formula used in cell F1

=MAP(A1:A5&B1:B5,LAMBDA(x,N(ISNUMBER(XMATCH($A$8&$B$8,x,0)))))

CodePudding user response：

I'd avoid concatenation because of possible false positives. I'd try:

Formula in C1:

=--(MMULT(--(A1:B5=D1:E1),{1;1})=2)

CodePudding user response：

Try this array formula in Sheet1:

=IFERROR(IF(MATCH(A1&B1,Sheet2!A:A&Sheet2!B:B,0)>0,1,0),0)