Nested IF CONTAINS, Return Blank When Contains Blank

Lori Flanigan

I've searched the Community and haven't found this situation, so I appreciate any help. I have a data set in which I'm trying to write a nested IF CONTAINS formula to return the primary program based on what is contained in the Project ID field.

My current formula is =IF(CONTAINS("ABC", [Project ID]:[Project ID}), "ABC", IF(CONTAINS("XYZ", [Project ID]:[Project ID]), "XYZ", IF(CONTAINS("KLM", [Project ID]:[Project ID]), "KLM", "")))

ABC is returned for every row because that is listed first in the formula.

The second formula I'm working on is to return the Active Program the person is working based on Program Shared To, Program Shared From, Shared Start Date, Shared End Date, and Term Date.

--When Program Shared To is XYZ and Program Shared From is ABC with no Shared End Date and no Term Date, the Active Program should return XYZ.

--When Program Shared To is XYZ and Program Shared From is ABC with a Shared End Date and no Term Date, the Active Program should return ABC.

--When a Term Date is included, Primary Program and Active Program should return blanks.

Thanks for your help!

Lori

Monique Odom-Stearn

Hello @Lori Flanigan,

Your first formula is searching the entire column for your criteria, instead of just the field in that row, so it's always returning ABC. Try changing it to the following, so it returns the Primary Program listed in that row and is blank if the term date is not blank:

=IF(NOT(ISBLANK([Term Date]@row)),"", IF(CONTAINS("ABC", [Project ID]@row), "ABC", IF(CONTAINS("XYZ", [Project ID]@row), "XYZ", IF(CONTAINS("KLM", [Project ID]@row), "KLM", ""))))

For your second request, try the following:

=IF(NOT(ISBLANK([Term Date]@row)), "", IF(ISBLANK([Shared End Date]@row), [Program Shared To]@row, [Program Shared From]@row))

Monique Odom-Stearn

Hello @Lori Flanigan,

Your first formula is searching the entire column for your criteria, instead of just the field in that row, so it's always returning ABC. Try changing it to the following, so it returns the Primary Program listed in that row and is blank if the term date is not blank:

=IF(NOT(ISBLANK([Term Date]@row)),"", IF(CONTAINS("ABC", [Project ID]@row), "ABC", IF(CONTAINS("XYZ", [Project ID]@row), "XYZ", IF(CONTAINS("KLM", [Project ID]@row), "KLM", ""))))

For your second request, try the following:

=IF(NOT(ISBLANK([Term Date]@row)), "", IF(ISBLANK([Shared End Date]@row), [Program Shared To]@row, [Program Shared From]@row))

Lori Flanigan

Monique, thank you so much for your quick and perfect formulas! I get confused about when the column must be used in the formula vs. using @row.

I appreciate your help!

Lori