# Nesting with IF, AND and OR functions

#### pnvn95

##### New Member
I am working on a project and I have two columns containing say "b" and "c". There are some conditions and the result of the condition is captured in column C. The problem is as shown below. If any column (A or B) from row one matches the same letter in column C (in this case "c"), then the result is "True Positive (TP). Similarly, for row two, if column A or B in row 2 matches with column C (in this case "b"), then the result should show True Negative (TN). Similarly, for row three, if any of the first two rows matches with column C (here "b"), then it is called True Negative (TN). Using IF, OR and AND statement, I could get this working until I got stumped when there is no data in column A or B (blank) along with column C having opposite letter from one of the columns (A or B) that is filled out. For example, in row 9, if column A is blank and column B is "c" and Column C is "b". The correct answer should be False Positive (FP). For the given formula, it gives me "No Match". Here is how my formulae looks (Couple of them)

Formula 1: =IF(OR(A1=C1,B1=C1),IF(C1="c","TP","TN"),IF(A1=B1,IF(A1="b","FN","FP"),"No Match"))

Formula 2: =IF(OR(AND(A1=C1,A1="b"),AND(B1=C1,B1="b")),"TN",IF(OR(AND(A1=C1,A1="c"),AND(B1=C1,B1="c")),"TP",IF(AND(A1=B1,A1="b",C1="c"),"FN",IF(AND(A1=B1,A1="c",C1="b"),"FP","No Match"))))

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>
 b c c TP b c b TN b b b TN b b c FN c b c TP c b b TN c c c TP c c b FP c b FP c b FP b c FN b c FN

<tbody>
</tbody>

Can anyone help me out to figure solution to this problem

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### jtakw

##### Well-known Member
Hi,

Try this:

E1 formula copied down.

#### jtakw

##### Well-known Member
Hi,

Ignore above post, use this version instead, it'll account for both Column A & B having BLANKS, to result in "No Match":

E1 formula copied down.

#### pnvn95

##### New Member
Thanks a lot Jtakw for your quick analysis and identifying a solution to my problem. Yes, it works!!! I am thrilled with the result. This forum is indeed a life saver for me.

#### jtakw

##### Well-known Member
Thanks a lot Jtakw for your quick analysis and identifying a solution to my problem. Yes, it works!!! I am thrilled with the result. This forum is indeed a life saver for me.
You're welcome, welcome to the forum,

Just to be sure, if either A or B is Blank, but the other is a match to C, if C is c, results in TP, if C is b, results in TN. Is that your desired outcome?

#### pnvn95

##### New Member
Hi jtakw:

That's correct. With either A or B being blank and one of them matches with C (if it is "c" then it is TP and if it is "b", TN). Yes, you provided me with the solution that is accurate and very useful.

#### pnvn95

##### New Member
Hi mfexcel:

Thanks for the link. Yes, I shall try using it by looking at the logic and shall adopt it as the challenges presents. Thanks for sharing this link. 