Returning BLANK date PRIORITY before Newest/Latest Date

**dluhut** · 07-11-2013, 04:43 PM

Hello,

I've been trying this for a few days and I'm very close to the answer, but not perfect. Hopefully excel experts here able to help me out.

I have a data that is in Table format sorted by Unique ID (smallest to largest), follow by Active Date (oldest to newest) and Inactive Date (oldest to newest).

I am able to get the active date with the following array formula

Please Login or Register  to view this content.

where $F2 is where my unique ID is.

My problem is on the inactive date!

Currently, I have my inactive date array formula;

Please Login or Register  to view this content.

But this give me PARTIALLY CORRECT ANSWER ONLY for those that do not have a 'blank' date.

Notice that on the attached spreadsheet (the orange), the inactive date which has a 'blank' date is "in between". Is there a formula where I can tell excel to take the 'blank' date as PRIORITY first, before taking other 'newest' date if there is no 'blank' date?

Your help is greatly appreciated!