Reputation: 15
I am trying to write an Excel function in order to compare the values of two columns and write in a third column a specific value, dependent of that comparison. The conditions that need to be simultaneously met are the following:
IF A1 = 0 AND B1 = 1 THEN C1 = 2 IF A1 = 0 AND B1 = 2 THEN C1 = 1 IF A1 = 2 AND B1 = 1 THEN C1 = 3 IF A1 = 2 AND B1 = 2 THEN C1 = 4
Is it possible to achieve this with nested IF's in Excel?
Many thanks
Upvotes: 0
Views: 51
Reputation: 3064
This should do what you want:
=IF(A1=0,IF(B1=1,2,IF(B1=2,1,"Invalid Input")),IF(A1=2,IF(B1=1,3,IF(B1=2,4,"Invalid Input")),"Invalid Input"))
Upvotes: 0
Reputation: 13044
What I would do, is to externalize those conditions. It helps you (and everyone else) whenever
The formula I use in Column C of the data table:
=IFERROR(FILTER(tblConditionC[C],(tblConditionC[A]=[@A]) *(tblConditionC[B]=[@B])),"no mapping")
Upvotes: 1
Reputation: 8375
So, based on what you state, this:
=if(and(a1=0,b1=1),2,if(and(a1=0,b1=2),1,if(and(a1=2,b1=1),3,if(and(a1=2,b1=2),4,"check"))))
Upvotes: 1