IF OR Statement Based On MATCH Function

[sanmisds1]

Hello,

In Sheet1, Col A and Col B has list of emails based on user's role. Sheet2 has just a handful of emails, about 2000. I am trying to write a formula that tells me if either of the emails exists in the lists of emails from Sheet2. For example, if I just need to see if email from Cell A2 in Sheet1 exists in Sheet2, I have the following formula.

=IF(ISNUMBER(MATCH(A2,'Sheet2'!$A$2:$A$2001,0)),1,"")

Next, I do a check for the following:

=IF(ISNUMBER(MATCH(B2,'Sheet2'!$A$2:$A$2001,0)),1,"")

I am trying to combine these two formulas into ONE. How is it possible. I tried something like this and it is not working.

=IF(OR(MATCH(A2,'Sheet2'!$A$2:$A$2000,0),MATCH(B2,'Sheet2'!$A$2:$A$2000,0)),1,"")

Appreciate your help. Thanks in advance.

 

[Jonmo1]

You still have to test each match for numeric value

So it would be
=IF(OR(ISNUMBER(MATCH(A2....)),ISNUMBER(MATCH(B2....))),1,"")

However, I'd recommend this instead
=IF(SUMUMPRODUCT(COUNTIF('Sheet2'!$A$2:$A$2001,A2:B2)),1,"")

 

[sanmisds1]

You still have to test each match for numeric value

So it would be
=IF(OR(ISNUMBER(MATCH(A2....)),ISNUMBER(MATCH(B2....))),1,"")

However, I'd recommend this instead
=IF(SUMUMPRODUCT(COUNTIF('Sheet2'!$A$2:$A$2001,A2:B2)),1,"")

Thank you. The first solution worked but I want to learn the second and recommended option as well.

What if the emails are in column A and C in Sheet1 instead of col A and col B? Please suggest. Thanks.

 

[Aladin Akyurek]

Also...

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$2000,$A2&"|"&$B2))

A2:A2000 must not have empty/blank cells...

 

[Jonmo1]

Thank you. The first solution worked but I want to learn the second and recommended option as well.

What if the emails are in column A and C in Sheet1 instead of col A and col B? Please suggest. Thanks.

It looks like I misspelled the sumproduct function there, hopefully you managed it. Should be
=IF(SUMPRODUCT(COUNTIF('Sheet2'!$A$2:$A$2001,A2:B2)),1,"")

Countif is another means to verify a value exists in a range. It's main advantage is it doesn't return an error if the value isn't there.
But it's normally only a single cell for the value to search for
COUNTIF('Sheet2'!$A$2:$A$2001,A2)

That formula I posted is basically a shortcut for summing 2 countif formulas.
So you can do that instead of
COUNTIF('Sheet2'!$A$2:$A$2001,A2)+COUNTIF('Sheet2'!$A$2:$A$2001,B2)


If your values to search for are not in consecutive cells, go with the original OR setup.
Or Aladin's formula in post #4
Or the countif+countif