Using IF for three scenarios

I want column J to look at two data sources (column F and column H) and determine if they agree. If they do I want it to say "Match" and if they don't, say "No."

In those columns the following items agree:
column F="Passed", column H= "Yes"
column F="Failed", column H="No"
column F="Missing", column H=#N/A.

Anything else and they do not agree.


I used this formula: =IF((AND(F3="Passed",H3="yes")),"Match","No"). But it only looks at one item.

I tried =IFERROR(IFS(AND(F3="Passed",H3="yes"),"Match",AND(F3="Failed",H3="No"),"Match",AND(F3="Missing",H3="#N/A"),"Match"),"No") but it just says "No" on all cells.

Someone gave me this one to try =arrayformula(if(len(J3:J),if(iferror((J3:J="Passed")*(L3:L="Yes"))+iferror((J3:J="Failed")*(L3:L="No"))+((J3:J="Missing")*(iferror(L3:L="#N/A")+(isna(L3:L)))),"Match","No"),)) but it has too few arguments.


Here is a screen shot:
capture.PNG



Can anyone help please?

So I tried this to solve this quickly before I had to leave for two hours but ran into an unexpected problem with #N/A. This works "passed" and "yes" as well as "failed" and "no." You can use this as a starting point.

=IF(AND(A1="Passed",B1="Yes"),"Match",IF(AND(A1="Failed",B1="No"),"Match",IF(AND(A1="Missing",B1="#N/A"),"Match","No")))

Excel recognizes #N/A as a special character I believe so you will have to play with it and probably use ISNA in the last part. If you can't get it and no one smarter than I posts a different solution then I will post a solution when I get home in two hours.

LearningByError.

Thank you. Taking out the #N/A worked. Thanks!