0

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

enter image description here

and in Sheet2 I have:

enter image description here

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)
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Manolete
  • 3,431
  • 7
  • 54
  • 92
  • @ImranMohammed I've read about it but I am a bit lost. Can you give me a hint? – Manolete Dec 05 '22 at 11:14
  • 1
    `=N(ISNUMBER(FIND(TEXTJOIN("|",,$A$1:$B$1),TEXTJOIN("|",,Sheet1!$A1:$B1))))` try this once and it will return 0 for the first, second, fourth and fifth cells while 1 for the third – Mayukh Bhattacharya Dec 05 '22 at 11:17

3 Answers3

3

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

enter image description here

Formula in C1:

=--(MMULT(--(A1:B5=D1:E1),{1;1})=2)
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • looks good but returns a column whereas I only need a value. ie. 1 in cell C1 as it has found a match – Manolete Dec 05 '22 at 14:03
  • 1
    @Manolete, you would return a column at all times if you plan to test all rows. What you can do is "hide" the zero. For example: `=REPT("1",--(MMULT(--(A1:B5=D1:E1),{1;1})=2))` – JvdV Dec 05 '22 at 14:07
  • @JvdV would you clarify what you mean by "possible false positives" and where would be such a scenario? I have seen the `XMATCH` with concatenation quite often, but now you make me think about the convenience of such an option. Thanks – David Leal Dec 05 '22 at 19:17
  • 1
    @DavidLeal, the problem is universal. Avoid concatenation if possible. Imagine two cells, one with 'AB' and one with 'CD'. Now compare this to the value 'A' and 'BCD'. Values are different but will result in a positive match if one just blindly concatenated these values. At least OP used a delimiter to limit the risk. I myself am still not a fan at all. – JvdV Dec 05 '22 at 19:20
2

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

enter image description here

• Formula used in cell D1

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

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

enter image description here

• Formula used in cell F1

=MAP(A1:A5&B1:B5,LAMBDA(x,N(ISNUMBER(XMATCH($A$8&$B$8,x,0)))))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
1

Try this array formula in Sheet1:

=IFERROR(IF(MATCH(A1&B1,Sheet2!A:A&Sheet2!B:B,0)>0,1,0),0)
Anastasiya-Romanova 秀
  • 3,326
  • 28
  • 48