Using IF Function with three scenarios

Question

I am having trouble writing a formula to get the outcome I want.

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 give "No" in every cell.

Someone suggested this: =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 says too few arguments.

Please help. I know it is possible, but I just can't make it work. Below is a screen shot.

Thank you!

Can anyone help please?

sergeibaklan · Accepted Answer

In general formula works, couple of points
- to check if the cell returns #N/A error you shall by ISNA() function, not by comparing with error text;
- IFS checks conditions one by one and stops working after the first TRUE, thus better error checking move on the first place
=IFERROR(IFS(AND(J2="Missing",ISNA(L2)), "Match", AND(J2="Passed",L2="Yes"),"Match", AND(J2="Failed",L2="No"),"Match",TRUE,"No"),"No")
Result is like here

&nbsp;
and attached

Forum Discussion

Using IF Function with three scenarios

2 Replies

Resources