Really need help with IF function to return one of three values based on data in two columns

[harry590_123]

Hi there

I really need some help with a formula that has been frustrating me for days so really hoping someone here will be able to help me out. I have two columns of data with "Yes" or "No" in them - one column represents if a task has been started (column A), the other represents if it has been completed (column B). I'm looking for a third column containing an IF formula to return three values: "Not started", "In progress" or "Complete" depending on what is in columns A and B.

There are only three possible ways the data in columns A and B will display:
Column A = "No" and column B = "No" (therefore want column C to return "Not started")
Column A = "Yes" and column B = "No" (therefore want column C to return "In progress")
Column A = "Yes" and column B = "Yes" (therefore want column C to return "Complete")

More simply, if column A = "No", it should always return "Not started" and if column B = "Yes", it should always return "Complete". But the "In progress" should be returned if it is started and not complete. I have gone through so many IF formula combinations but here are some of my latest attempts:

=IF(A1="No","NOT STARTED",
IF(AND(A1="Yes",B1="NO"),"IN PROGRESS",
IF(B1="Yes","COMPLETE")))

Returns only COMPLETE or FALSE

=IFERROR(IF(A1="No","NOT STARTED",
IF(AND(A1="Yes",B1="NO"),"IN PROGRESS",
IF(B1="Yes","COMPLETE"))),"")

Returns only COMPLETE or FALSE

=IFERROR(IF(AND(A1="No",B1="No"),"NOT STARTED",
IF(AND(A1="Yes",B1="Yes"),"COMPLETE","IN PROGRESS")),"")

Returns COMPLETE and IN PROGRESS, but never NOT STARTED

I'm not great with Excel so I really apologise if I'm doing something completely stupid here. Perhaps IF isn't even the best way to be going about this. I would be so grateful for any help or advice with this one though as I've been trying and failing for so long. I've been searching the internet and these forums too but clearly not doing a great job.

I'm using Excel 2016.

Many thanks in advance for any help!

 

[CalcSux78]

Is it possible that A is "No" and B is "Yes"? If so, this nested IF statement should work. Otherwise, change "Undefined" to ""

Excel 2010
	A	B	C
1	No	No	Not started
2	Yes	No	In progress
3	Yes	Yes	Complete
4	No	Yes	Undefined
Sheet1

Cell Formulas
Range	Formula
C1	=IF(A1="Yes",IF(B1="Yes","Complete","In progress"),IF(B1="No","Not started","Undefined"))

 

[harry590_123]

Hi CalcSux78 - thank you so much!!

It wouldn't be possible for A to be "No" and B to be "Yes" so I tried your second suggestion:

=IF(A1="Yes",IF(B1="Yes","Complete","In progress"),IF(B1="No","Not started",""))

This worked perfectly! Thank you, thank you, thank you! You have saved me hours of torture.

What is strange though, in my description of what I was trying to do I did simplify the example slightly. The table I am working with actually has far more columns (goes up to N) and rather than A and B the columns I'm looking at are J and K. When I tried your suggestion in my actual spreadsheet which looked like this in the cell N1:

=IF(J1="Yes",IF(K1="Yes","Complete","In progress"),IF(K1="No","Not started",""))

This actually caused cells that should be "Not started" to be blank. I didn't think it would make a difference where the cells were which is why I simplified what I was trying to do in my example.

I have got around this by putting "Not started" in the formula again instead of "". It is working perfectly!

I really can't thank you enough

 

[CalcSux78]

Glad it worked for you.. Remember to pay it forward. Answer a question if you have a moment in the future.