Using if and or formula for numbers in 2 columns to match numbers in 3 different columns

[dougkpga]

I have numbers in Columns A and B and i want to get a Y or N if these numbers are found in Columns C D or E.

My formula is

=IF(OR(A2=C2,A2=D2,A2=E2,B2=C2,B2=D2,B2=E2),"Y","N")

But it shows Y all the way down.

Also if all the Columns are blank I need the formula to also give me a blank in the Column where the Y and N occur.

I have put the 6 columns on the sample spreadsheet and also what the correct results should be.

Thanks in advance for any help.

[FDibbins]

It's not working because you have empty cells that match empty cells, you need to address that 1st...
=IF(OR(COUNT(A2:B2)=0,COUNT(C2:E2)=0),"N",IF(OR(MATCH(B2,C2:E2,0),MATCH(A2,C2:E2,0)),"Y","N"))

[dougkpga]

Thanks a lot Ford, I just didn't know how to make the formula skip the blank cells.

I'm still learning about these advanced formulas (which are probably not advanced for you).

Thanks for your help.

[FDibbins]

Not a problem, happy to help.

Most formulas a really not as advanced as they look. You just have to build and test the parts individually, then figure out how to combine them.

- how to test for A and B being empty...
COUNT(A2:B2)=0

- how to test for C:E being empty...
COUNT(C2:E2)=0)

- Now, how to test for both, and what to do if either set is empty...
OR(COUNT(A2:B2)=0,COUNT(C2:E2)=0),"N"

The rest, I just took a different approach for the match...
OR(MATCH(B2,C2:E2,0),MATCH(A2,C2:E2,0))

[FDibbins]

Thanks for the feedback