If Function - multiple scenarios

Hi Jaime

Hopefully I have understood right.

If you are using Office 365 then you can try this formula I've drafted into cell J2 and autofill down. It should do the trick.

=IFERROR(IFS(AND(F2="Passed", H2="Yes"), "Match", AND(F2="Failed",H2="No"), "Match", AND(F2="Missing",H2="N/A"), "Match"), "No")

You will also need to change #N/A to N/A in your cells. The issue for #N/A is that it's something Excel uses so it won't recogise it in your formula as text. Better to use N/A which will not conflict.

I've attached a sample spreadsheet to demo the formula in action and have added conditional formatting to show you when it matches.

Let us know how you go and if this is the solution to your problem?

Cheers

Damien

I suggest to introduce two more columns for simplifying the formula. The formula "CHOOSE" will help. As the column H contains error message (#N/A), I don't have ideas to compare with "IF". To solve this, I use "IFERROR" and "VLOOKUP" at the same time and then use "CHOOSE". You may refer to the file attached.