IF AND Formula for 5 Options

John- Michael Diedrich

Hey Community, please help me figure this out.

We have 5 options for types of projects and they are the following:

Technical Services Projects

Technical Services Programs

Enterprise Projects

Enterprise Programs

ODE

We are trying to output one of those five options based on the columns Project Type and Program Type.

Project type can be either Technical Services Projects or Enterprise Projects, while Program Type can be either Technical Services Programs or Enterprise Programs. The last option (assuming this would be the false part of the IF statement) is ODE.

See picture below to get an idea of the columns, we are trying to output it in the OPPTY Type Master column (ignore the red row).

Any help on this is greatly appreciated!!

Jeff Reisman

@John- Michael Diedrich

So if I understand correctly, they select one of two values from "Project Type" column, OR they select one of two values from "Program Type" column, and you want whichever one thing they've chosen to be displayed in "OPPTY Type Master" column, unless they've chosen nothing from either column, in which case the "OPPTY Type Master" value should be ODE. Correct?

In your OPPTY Type Master column, try the following:

=IF(ISTEXT([Project Type]@row), [Project Type]@row, IF(ISTEXT([Program Type]@row), [Program Type]@row, "ODE"))

In English, if there's a text value in Project Type, show that text here; otherwise, if there's a text value in Program Type, show that text here; otherwise (no text in either column,) show "ODE" here.

Jeff Reisman

@John- Michael Diedrich

So if I understand correctly, they select one of two values from "Project Type" column, OR they select one of two values from "Program Type" column, and you want whichever one thing they've chosen to be displayed in "OPPTY Type Master" column, unless they've chosen nothing from either column, in which case the "OPPTY Type Master" value should be ODE. Correct?

In your OPPTY Type Master column, try the following:

=IF(ISTEXT([Project Type]@row), [Project Type]@row, IF(ISTEXT([Program Type]@row), [Program Type]@row, "ODE"))

In English, if there's a text value in Project Type, show that text here; otherwise, if there's a text value in Program Type, show that text here; otherwise (no text in either column,) show "ODE" here.