Solved excel if condition, please help

April 12, 2012 at 06:26:19
Specs: Windows 7, 2gb
i have got data in an excel sheet in 3 columns, A,B,C

What i want is B to be populated automaticaly.

I want B to be populated if the following conditions are met,
PART 1
if A = 2012 and C = 1 then B = 1
if A=2011 and C =1 then B =2
if A =2010 and C= 1 then B=3
PART 2
if A = 2011 and C = 2 then B = 1
if A=2010 and C =2 then B =2
if A =2019 and C= 2 then B=3
PART 3
if A = 2010 and C = 3 then B = 1
if A=2009 and C =3 then B =2
if A =2008 and C= 3 then B=3
PART 4
if A = 2009 and C = 4 then B = 1
if A=2008 and C =4 then B =2
if A =2007 and C= 4 then B=3

PART 5
if A = 2008 and C = 5 then B = 1
if A=2009 and C =5 then B =2
if A =2006 and C= 5 then B=3

Please help me, can all the parts be combined in one formula


See More: excel if condition, please help

Report •


#1
April 12, 2012 at 14:38:55
Are you sure your data is correct?

You have the year 2019 in Part 2, is this correct?

Please read this How-To, it explains how to post your data.

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

#2
April 12, 2012 at 21:36:04
should be 2009

Report •

#3
April 13, 2012 at 03:57:01
✔ Best Answer
Here is a veeerrry long =IF() function, if you do not have 2007 or 2010 it won't work.

=IF(AND(A1=2006,C1=5),3,IF(AND(A1=2007,C1=4),3,IF(AND(A1=2008,C1=5),1,IF(AND(A1=2008,C1=4),2,IF(AND(A1=2008,C1=3),3,IF(AND(A1=2009,C1=4),1,IF(AND(A1=2009,C1=3),2,IF(AND(A1=2009,C1=5),2,IF(AND(A1=2009,C1=2),3,IF(AND(A1=2010,C1=3),1,IF(AND(A1=2010,C1=2),2,IF(AND(A1=2010,C1=1),3,IF(AND(A1=2011,C1=2),1,IF(AND(A1=2011,C1=1),2,IF(AND(A1=2012,C1=1),1,""))))))))))))))

A better option would be to build a table and use a =VLOOKUP() function with =MATCH()

Something like:

On Sheet 2 enter your Table:

  
     A    B       C       D       E       F
1) 	  1	  2	  3	  4	  5
2) 2006					  3
3) 2007				  3	  2
4) 2008			  3	  2	  1
5) 2009		  3	  2	  1	
6) 2010	  3  	  2	  1		
7) 2011	  2	  1			
8) 2012	  1				

On Sheet 1, Cell B1 enter the formula:

=VLOOKUP(A1,Sheet2!$A$2:$F$9,MATCH(C1,Sheet2!$B$1:$F$1,0)+1,FALSE)

Then any date you put in Cell A1 between 2006 & 2012
and any number between 1 & 5 in Cell C1
will be matched and the intersecting number
will be returned.

Much neater then the long =IF() function.

MIKE

http://www.skeptic.com/


Report •

Related Solutions


Ask Question