 April 12, 2012 at 06:26:19
Specs: Windows 7, 2gb
 i have got data in an excel sheet in 3 columns, A,B,CWhat i want is B to be populated automaticaly.I want B to be populated if the following conditions are met,PART 1if A = 2012 and C = 1 then B = 1if A=2011 and C =1 then B =2if A =2010 and C= 1 then B=3PART 2if A = 2011 and C = 2 then B = 1if A=2010 and C =2 then B =2if A =2019 and C= 2 then B=3PART 3if A = 2010 and C = 3 then B = 1if A=2009 and C =3 then B =2if A =2008 and C= 3 then B=3PART 4if A = 2009 and C = 4 then B = 1if A=2008 and C =4 then B =2if A =2007 and C= 4 then B=3PART 5if A = 2008 and C = 5 then B = 1if A=2009 and C =5 then B =2if A =2006 and C= 5 then B=3Please help me, can all the parts be combined in one formula #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...MIKEhttp://www.skeptic.com/

Report •

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

Report •

#3 April 13, 2012 at 03:57:01
 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 & 2012and any number between 1 & 5 in Cell C1will be matched and the intersecting numberwill be returned.Much neater then the long =IF() function.MIKEhttp://www.skeptic.com/ 