I have a data set having IN & OUT columns with text string.

Compared text “Yes“, “NA“, “N/A“, “No“, “Partial“.

If it is matching both the columns then 0 else 1.

If any column having NA or N/A that also be considered as “Yes“.

Some times data will receive with Comments.

Some how I managed with this formula. Any better solution rather than this.

Thank you.

=N(IF(ISERROR(IFERROR(SEARCH("Yes",E3),IFERROR(SEARCH("n/A",E3),SEARCH("NA",E3)))),IF(ISERR(SEARCH("Partial",E3)),"No","Partial"),"Yes")<>IF(ISERROR(IFERROR(SEARCH("Yes",F3),IFERROR(SEARCH("N/A",F3),SEARCH("NA",F3)))),IF(ISERR(SEARCH("Partial",F3)),"No","Partial"),"Yes"))

IN
Partial- needs to be considered
Yes – worked well
Partial- needs to be considered
NA
N/A – Not applicable
NA – Not applicable
NA- not applicable
No

OUT
Yes – Worked well
Yes
Partial – “applicable”
Yes – Worked well
Yes- Worked well
N/A
NO – not applicable
Yes