Spreadsheet: 598206d1542305667-retrieving-another-text-string-based-on-multiple-criteria-formula-requires-update-program-changed-into-2018-11-13
Sheet: Sheet1

SAMPLE ID | Year | Program Change | First Annual StartDate | First Annual EndDate | Last Annual StartDate | Last Annual EndDate | Middle StartDate | Middle EndDate | Desired Result  (M6 to M8) | Program returned into (current formula used)
15743 | 2011-2012 | 1 | 2011-09-01 00:00:00 | 2011-12-30 00:00:00 | 2012-01-02 00:00:00 | 2012-04-30 00:00:00 |  |  | Allocation |  | Incorrect |  | Reference Table
23794 | 2010-2011 | 2 | 2010-08-23 00:00:00 | 2010-12-22 00:00:00 | 2011-01-03 00:00:00 | 2011-05-31 00:00:00 |  |  | Computers |  | Incorrect |  | ID | Year | Program | Registration Status | Start Date | End Date
21086 | 2016-2017 | 2 | 2017-01-02 00:00:00 | 2017-04-30 00:00:00 | 2017-05-01 00:00:00 | 2017-08-31 00:00:00 |  |  | Cup |  | Correct |  | 18390 | 2015-2016 | Study Group | Completed | 2015-08-31 00:00:00 | 2015-12-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18390 | 2015-2016 | Finished Product | Completed | 2016-01-02 00:00:00 | 2016-04-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18390 | 2015-2016 | General | Completed | 2016-05-01 00:00:00 | 2016-06-30 00:00:00
 |  |  |  |  |  |  |  |  | Current [NAME] used  Desired result would be to use a formula that takes the next program returned into (column O) after all "program change" registration statuses (Column P) from the reference table to the right.  However, two constraints to mention,   1. There may be more than one program change in any year so formula must take the program after all other programs were changed if any.  2. If the program returned into is only in the next year or any year thereafter (column B), the formula will still extract the program returned into regardless. |  |  |  | 18390 | 2016-2017 | Enhanced | In Progress | 2018-01-02 00:00:00 | 2018-01-08 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18028 | 2010-2011 | Basic | Completed | 2009-09-08 00:00:00 | 2011-04-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18028 | 2010-2011 | Advanced | Completed | 2011-05-01 00:00:00 | 2011-08-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18028 | 2011-2012 | Proper | Withdrawn by self | 2011-09-01 00:00:00 | 2012-06-27 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18028 | 2012-2013 | Total | Completed | 2012-09-01 00:00:00 | 2013-06-27 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18390 | 2013-2014 | Study Group | In Progress | 2011-09-01 00:00:00 | 2014-06-27 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18390 | 2016-2017 | Local | Completed | 2016-07-01 00:00:00 | 2017-08-05 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18028 | 2013-2014 | Furnisher | Withdrawn by self | 2013-09-10 00:00:00 | 2014-01-04 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 18028 | 2013-2014 | Craftsman | In Progress | 2014-02-06 00:00:00 | 2014-06-28 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 15743 | 2010-2011 | Specific | Program Change | 2009-09-09 00:00:00 | 2011-04-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 15743 | 2011-2012 | [NAME] | Program Change | 2011-09-01 00:00:00 | 2011-12-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 15743 | 2011-2012 | Allocation | Completed | 2012-01-02 00:00:00 | 2012-04-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 23974 | 2011-2012 | Computers | Withdrawn by self | 2011-08-15 00:00:00 | 2012-02-16 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 23974 | 2013-2014 | Buildings | Withdrawn by self | 2013-08-20 00:00:00 | 2014-06-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 23974 | 2010-2011 | Sensor | Program Change | 2010-08-23 00:00:00 | 2010-12-22 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 23974 | 2010-2011 | Application | Program Change | 2011-01-03 00:00:00 | 2011-05-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 21086 | 2016-2017 | Stock | Withdrawn by self | 2016-01-11 00:00:00 | 2016-12-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 21086 | 2010-2011 | Adobe | Withdrawn by self | 2011-01-10 00:00:00 | 2011-08-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 21086 | 2015-2016 | Scottles | Withdrawn by self | 2015-08-31 00:00:00 | 2015-12-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 21086 | 2016-2017 | Paper | Program Change | 2017-01-02 00:00:00 | 2017-04-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 21086 | 2016-2017 | Shake | Program Change | 2017-05-01 00:00:00 | 2017-08-31 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 21086 | 2014-2015 | Bulletin | Program Change | 2014-09-01 00:00:00 | 2015-08-16 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | 21086 | 2017-2018 | Cup | In Progress | 2017-09-01 00:00:00 | 2018-04-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  | … | … | … | … | … | …
 |  |  |  |  |  |  |  |  |  |  |  |  | ...NOTE THAT LIST IS VERY LONG with many more rows.