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

ID | Year | Program Change | Completed | First Annual StartDate | First Annual EndDate | Last Annual StartDate | Last Annual EndDate | Middle StartDate | Middle EndDate | Desired Result  (M6 to M8) | Current [NAME] used | Note that current formula extracts the next program returned into after all other programs are "completed" in any given year specified in Column B (year). This formula is working flawlessly for programs returned into after "completed".
18390 | 2015-2016 | 0 | 3 | 2015-08-31 00:00:00 | 2015-12-31 00:00:00 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00 | 2016-01-02 00:00:00 | 2016-04-30 00:00:00
18028 | 2010-2011 | 0 | 2 | 2009-09-08 00:00:00 | 2011-04-30 00:00:00 | 2011-05-01 00:00:00 | 2011-08-31 00:00:00 |  |  |  |  |  | Reference Table
18028 | 2012-2013 | 0 | 1 | 2012-09-01 00:00:00 | 2013-06-27 00:00:00 |  |  |  |  |  |  |  | ID | Year | Program | Registration Status | Start Date | End Date
18028 | 2013-2014 | 0 | 0 | 2013-09-10 00:00:00 | 2014-01-04 00:00:00 | 2014-02-06 00:00:00 | 2014-06-28 00:00:00 |  |  | (blank) |  |  | 18390 | 2015-2016 | Study Group | Completed | 2015-08-31 00:00:00 | 2015-12-31 00:00:00
15743 | 2011-2012 | 1 | 0 | 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 | 18390 | 2015-2016 | Finished Product | Completed | 2016-01-02 00:00:00 | 2016-04-30 00:00:00
23794 | 2010-2011 | 2 | 0 | 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 | 18390 | 2015-2016 | General | Completed | 2016-05-01 00:00:00 | 2016-06-30 00:00:00
21086 | 2016-2017 | 2 | 0 | 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 | 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
 |  |  |  |  |  |  |  |  |  | [NAME] (highlighted) I thought that I would be able to apply the formula used in cells O2 to O6 for other statuses like Program Change instead of Completed. The formula (after changing "Completed" to "Program Change" is working for most ID's, but is returning blanks at times (cell L6), as shown for ID 15743 (Column A) for year 2011-2012, which should state allocation. Note that the formula for "completed" (current formula) is used in another column in the actual data set. I put both formulas in one column for demonstration purposes |  |  | 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.