Combinging two IF statements

Yeimi Vasquez

Hi,

I need help combining two IF statements. They are constructed the same, but I want them to be one entire formula for a specific cell rather than two seperate formulas. They both work serpartley, but I keep getting errors when trying to combine them.

Essentialy, it is when the Department column is blank it will reference the Facility Cell, then it will refernce a seperate sheet (Department Assignments) to assing the correct Project Manager to the Project Manager column based on the facility. I want it to do the same for Statememtn 2, but instead for a differnent department.

Statement 1

=IF([Project Title]@row = "Title", "Project Manager", IFERROR(IF(ISBLANK(Facility@row), INDEX({Department Assignments PM}, MATCH(Department@row, {Department Assignments Department}, 0)), INDEX({Department Assignments PM}, MATCH(Facility@row, {Department Assignments Department}, 0))), ""))

Statement 2

=IF([Project Title]@row = "Title", "Project Manager", IFERROR(IF(ISBLANK([Communications & Marketing]@row), INDEX({Department Assignments PM}, MATCH(Department@row, {Department Assignments Department}, 0)), INDEX({Department Assignments PM}, MATCH([Communications & Marketing]@row, {Department Assignments Department}, 0))), ""))

Kelly Drake

IF statements need to have a binary.... especially when you use the IFERROR (which i actually think isn't necessary since you're nesting IFs. But also - this will result in just one contact and the order will matter here so you have two options

IF Facility contacts are more important to pull:

= IF([Project Title]@row = "Title", "Project Manager", IF(NOT(ISBLANK(Facility@row)),  INDEX({Department Assignments PM}, MATCH(Facility@row, {Department Assignments Department}, 0)), IF(NOT(ISBLANK([Communications & Marketing]@row)), INDEX({Department Assignments PM}, MATCH([Communications & Marketing]@row, {Department Assignments Department}, 0)), INDEX({Department Assignments PM}, MATCH(Department@row, {Department Assignments Department}, 0)))

IF Comms contacts are more important

= IF([Project Title]@row = "Title", "Project Manager", IF(NOT(ISBLANK([Communications & Marketing]@row)), INDEX({Department Assignments PM}, MATCH([Communications & Marketing]@row, {Department Assignments Department}, 0)), IF(NOT(ISBLANK(Facility@row)),  INDEX({Department Assignments PM}, MATCH(Facility@row, {Department Assignments Department}, 0)), INDEX({Department Assignments PM}, MATCH(Department@row, {Department Assignments Department}, 0)))

Kelly Drake

IF statements need to have a binary.... especially when you use the IFERROR (which i actually think isn't necessary since you're nesting IFs. But also - this will result in just one contact and the order will matter here so you have two options

IF Facility contacts are more important to pull:

= IF([Project Title]@row = "Title", "Project Manager", IF(NOT(ISBLANK(Facility@row)),  INDEX({Department Assignments PM}, MATCH(Facility@row, {Department Assignments Department}, 0)), IF(NOT(ISBLANK([Communications & Marketing]@row)), INDEX({Department Assignments PM}, MATCH([Communications & Marketing]@row, {Department Assignments Department}, 0)), INDEX({Department Assignments PM}, MATCH(Department@row, {Department Assignments Department}, 0)))

IF Comms contacts are more important

= IF([Project Title]@row = "Title", "Project Manager", IF(NOT(ISBLANK([Communications & Marketing]@row)), INDEX({Department Assignments PM}, MATCH([Communications & Marketing]@row, {Department Assignments Department}, 0)), IF(NOT(ISBLANK(Facility@row)),  INDEX({Department Assignments PM}, MATCH(Facility@row, {Department Assignments Department}, 0)), INDEX({Department Assignments PM}, MATCH(Department@row, {Department Assignments Department}, 0)))

Yeimi Vasquez

Gotcha, that makes sense! Thank you so much 😀