Simple Match/Mismatch Formula

[ronakpatel]

All,

I have a very simple request that I can't seem to figure out myself. I have attached the Excel spreadsheet to make the request easier to understand.

I have a spreadsheet with 5 columns where I'm comparing the values of Columns A & B to Columns C through E. If the values in Columns A & B are not present in Columns C through E, then Column F should output "Mismatch". If values in Columns A & B match values in Columns C through E, then Column F outputs "Match".


I appreciate all the help!

[etaf]

you can use a countif()
BUT i dont understand the mismatch in the example
ROW 4
why a mismatch - A4 = C4

[ronakpatel]

Hi etaf,

In row 4, the code in Column B does not match anything in columns C through E, therefore it's supposed to output "MISMATCH"

[etaf]

OK, Got it

[etaf]

what about blank cells in A or B
If there is no blank in C to E is that a mismatch ?
ROW 6
or do we ignore blanks
=AND(IF(A2="",1,COUNTIF(C2:E2,A2)>0),IF(B2="",1,COUNTIF(C2:E2,B2)>0))

[ronakpatel]

No, row 6 is a match. If there are values present in either Column A or Column B, then they must also be present in Columns C through E for the row to be a match. Any blanks in Row A or B can be ignored.

[etaf]

so this should work
=AND(IF(A2="",1,COUNTIF(C2:E2,A2)>0),IF(B2="",1,COUNTIF(C2:E2,B2)>0))

=if( AND(IF(A2="",1,COUNTIF(C2:E2,A2)>0),IF(B2="",1,COUNTIF(C2:E2,B2)>0)), "match", "mismatch")

i suspect an easier way, will see

[ronakpatel]

Thank you etaf. That formula works very well!

[etaf]

you are welcome