excel - Match multiple columns over multiple sheets?

Question

Sheet1
qwerty,1234
asdf,2345
zxcv,3456

Sheet2
uiop,0987
qwerty,6789
qwerty,1234
fghjk,4567
zxcv,3456
zxcv,7890

How to highlight the row in Sheet1 iff Sheet2 have at least 1 row that matches both columns? In this example, the rows qwerty,1234 and zxcv,3456 should be highlighted.

I need both data to be on the same row to match.

=NOT(ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE))) matches only 1 column, how to do it for both?

question from:https://stackoverflow.com/questions/65912143/match-multiple-columns-over-multiple-sheets

Answer 1

You can also use Match() function like below. But COUNTIFS() would be best as it will be faster.

=MATCH($A1&$B1,Sheet2!$A$1:$A$5&Sheet2!$B$1:$B$5,0)