If cell contains certain text, then return value

[syang]

I need help with this formula:

=IF(A1="Teacher","TEACHER","")&IF(B1="Student","STUDENT","")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Teacher[/TD]
[TD][/TD]
[TD]TEACHER[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Student[/TD]
[TD]STUDENT[/TD]
[/TR]
[TR]
[TD]Teacher[/TD]
[TD]Student[/TD]
[TD]TEACHERSTUDENT[/TD]
[/TR]
</tbody>[/TABLE]

The first and second rows do what I need, but I want the last row to return "TEACHER, STUDENT" instead of "TEACHERSTUDENT."

Any help is appreciated...thank you!!

 

[etaf]

IF( AND( A1="Teacher", B1=Student) , "Teacher, Student", If( And(A1="Teacher",B1=""), "TEACHER", IF(And(A1="",B1="Student"), "STUDENT", "")))

 

[syang]

Thank you! It gave me an error but I made some revisions and it worked.

=IF(AND(A1="Teacher",B1="Student"), "TEACHER, STUDENT", IF(AND(A1="Teacher",B1=""), "TEACHER", IF(AND(A1="",B1="Student"), "STUDENT", "")))

 

[etaf]

excellent

 

[njimack]

Another option for you...

Excel Workbook
	A	B	C
1	Teacher		Teacher
2		Student	Student
3	Teacher	Student	Teacher, Student
Sheet1

 

[Rick Rothstein]

And one more option for you to consider...

=MID(IF(A1="","",", "&A1)&IF(B1="","",", "&B1),3,999)