Compare both the columns from Text values

Question

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	**OUT **
Partial- needs to be considered	Yes - Worked well
Yes - worked well	Yes
Partial- needs to be considered	Partial - "applied"
NA	Yes - Worked well
N/A - Not applicable	Yes- Worked well
NA - Not applicable	N/A
NA- not applicable	NO - not applicable
No	Yes

Answer 1

This is a solution that spills the result in Office 365:

=LET(x,TEXTSPLIT(B3:C10,{" ","-"}),y,IF(ISNA(XMATCH(x,{"NA","N/A"})),x,"Yes"),N(TAKE(y,,1)<>DROP(y,,1)))

Where x is the Textsplit of the whole range on (first found) space character or minus sign in each string; resulting in the first word of each cell.

y matches these strings to equal N/A or NA, if so return Yes, else return the string.

Last we compare the strings of the first and last column of y

Answer 2

Maybe simpler workaround works for all words before the - sign.

=N(IF(OR(TRIM(TAKE(TEXTSPLIT(A16,"-"),,1))={"NA","N/A"}),"YES",TRIM(TAKE(TEXTSPLIT(A16,"-"),,1)))<>IF(OR(TRIM(TAKE(TEXTSPLIT(B16,"-"),,1))={"NA","N/A"}),"YES",TRIM(TAKE(TEXTSPLIT(B16,"-"),,1))))