Highly Complex Conditional Formula Help Needed

**Aimeeeshaw** · 07-02-2013, 07:01 PM

Hello! New user, first time posting. Nice to meet you all! I'll get right to my question!

Would it be possible to pack this into one supercharged formula via COUNTIFS or VLOOKUP with nested IFS etc.? I am having a heck of a time. Any help would be appreciated. I use Excel 2010.

Criteria 1: If Column X:X is not blank, Column Z:Z is blank, and Column AB:AB is blank, then pull date from Column N:N
Criteria 2: If Column Z:Z is not blank, Column X:X is blank, and Column AB:AB is blank, then pull date from Column O:O
Criteria 3: If Column AB:AB is not blank, Column X:X is blank, and Column Z:Z is blank, and BOTH Columns N:N and O:O are not blank, then pull the LATTER of the dates in Columns N:N and O:O

My undying gratitude to any of you heavyweights that can help me solve this riddle. I don't seem to be grasping the essence of at least one factor of this formula that could make it solvable.

Internet hugs, and Happy Fourth of July for any fellow Americans here (almost)!
Aimee in Madison

**Richard Buttrey** · 07-02-2013, 07:21 PM

Hi, and welcome to the forum.

Maybe....

Formula:



=IF(AND(X1<>"",Z1="",AB1=""),N1,IF(AND(Z1<>"",X1="",AB1=""),O1,IF(AND(AB1<>"",X1="",Z1="",N1<>"",O1<>""),MAX(N1,O1),"")))

**ConneXionLost** · 07-02-2013, 07:22 PM

What answer do you want if AB is not blank but either N or O are blank?

What answer do you want if X is not blank but N is blank? (Same for Z and O)?

**Aimeeeshaw** · 07-03-2013, 12:13 PM

@Connie the answer to both scenarios would be "", which Richard's formula covers by exclusion. I also added an IFERROR="" to the formula of Richard's for good measure. I checked the work and it is functioning perfectly for our needs.

@Richard Thanks so much. I owe ya one.