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=3PART 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=3Please help me, can all the parts be combined in one formula

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

should be 2009

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 1On 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

Ask Your Question

Weekly Poll