Spreadsheet: 599681d1543249418-retrieving-another-text-string-based-on-multiple-criteria-formula-requires-update-program-changes-2018-11-26
Sheet: Sheet1

SAMPLE ID | Year | Program [NAME]) | First Annual StartDate | First Annual EndDate | Last Annual StartDate | Last Annual EndDate | Middle StartDate | Middle EndDate | Desired Result Program change FROM (J6 to L8) |  |  |  | Desired Result Program change TO (M6 to Q8)
15743 | 2011-2012 | 3 | 2011-09-01 00:00:00 | 2011-12-30 00:00:00 | 2012-01-02 00:00:00 | 2012-04-30 00:00:00 |  |  | [NAME] | Allocation | Final | (blank) | Allocation | Final | Small | (blank) |  | Reference Table
23974 | 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 |  |  | Sensor | Applicaton | (blank) | (blank) | Application | Computers | (blank) | (blank) |  | 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 |  |  | Paper | Shake | (blank) | (blank) | Shake | Cup | (blank) | (blank) |  | 18390 | 2015-2016 | Study Group | Completed | 2015-08-31 00:00:00 | 2015-12-31 00:00:00
 |  |  |  |  |  |  |  |  | 1st program change | 2nd program change | 3rd program change | 4th program change | 1st program returned into | 2nd program returned into | 3rd program returned into | 4th program returned into |  | 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
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 18390 | 2016-2017 | Enhanced | In Progress | 2018-01-02 00:00:00 | 2018-01-08 00:00:00
 |  |  |  |  |  |  |  |  | Desired results idenify all programs changed from and returned into based on the date (chronological order). If the program changed to shows up in a later year than column B, that program is still considered and recorded as shown in the desired results above. |  |  |  |  |  |  |  |  | 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 Findings | 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 | Program Change | 2012-01-02 00:00:00 | 2012-04-30 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 15743 | 2011-2012 | Final | Program Change | 2012-05-04 00:00:00 | 2012-10-01 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 15743 | 2011-2012 | Small | Withdrawn by self | 2012-10-02 00:00:00 | 2012-11-03 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.