Spreadsheet: 600016d1543420997-retrieving-another-text-string-based-on-multiple-criteria-formula-requires-update-program-changes-after-withdrawals-2018-11-28-update
Sheet: Sheet1

SAMPLE ID | Year | Desired Result  Program change after withdrawal FROM (C2 to E5) |  |  | Desired Result  Program change after withdrawal TO (F2 to H5)
18390 | 2015-2016 | Study Group | Finished Product | General | Finished Product | General | Enhanced (2016-2017) |  | Reference Table
18028 | 2011-2012 | Advanced | remained |  | remained | Total (2012-2013) |  |  | ID | Year | Program | Registration Status | Start Date | End Date
18028 | 2012-2013 | Total |  |  | (did not return) |  |  |  | 18390 | 2015-2016 | Study Group | Auth. Withdraw | 2015-08-31 00:00:00 | 2015-12-31 00:00:00
23974 | 2010-2011 |  |  |  |  |  |  |  | 18390 | 2015-2016 | Finished Product | Withdraw 1 | 2016-01-02 00:00:00 | 2016-04-30 00:00:00
21086 | 2016-2017 | Shake |  |  | Cup (2017-2018) |  |  |  | 18390 | 2015-2016 | General | Withdraw 4 | 2016-05-01 00:00:00 | 2016-06-30 00:00:00
 |  | 1st program change (if any) after withdrawal | 2nd program change (if any) after withdrawal | 3rd program change (if any) after withdrawal | 1st program after withdrawal returned into (if any) | 2nd program after withdrawal returned into (if any) | 3rd program after withdrawal returned into (if any) |  | 18390 | 2016-2017 | Enhanced | In Progress | 2017-01-02 00:00:00 | 2017-01-08 00:00:00
 |  |  |  |  |  |  |  |  | 18028 | 2010-2011 | Basic | Withdraw 3 | 2009-09-08 00:00:00 | 2011-04-30 00:00:00
 |  |  |  |  |  |  |  |  | 18028 | 2011-2012 | Advanced | Withdraw 2 | 2011-05-01 00:00:00 | 2011-08-31 00:00:00
 |  | If the program does not change after a one of 4 types of withdrawals then the cell should return "remained". (ID 18028 - year 2011-2012)   If program returned is a later year, the year is shown next to the name of the program returned into (or remained) with the year next to when the program was returned. (ID 18028 - year 2011-2012)  If there was no type of withdrawal in the year, then all desired cells should remain blank ("") (ID 23974).  Sometimes an ID can have a withdrawal and there no later date listed for that same ID at a later date. In this case, the desired result should read "(did not return)". (ID 18028 - year 2012-2013)  Note that 3 withdrawals is the maximum number of withdrawals that can occur in one year. |  |  |  |  |  |  | 18028 | 2011-2012 | Advanced | Withdraw 3 | 2011-09-01 00:00:00 | 2012-06-27 00:00:00
 |  |  |  |  |  |  |  |  | 18028 | 2012-2013 | Total | Withdraw 1 | 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 | Productive withdrawal | 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 | Withdraw 3 | 2009-09-09 00:00:00 | 2011-04-30 00:00:00
 |  |  |  |  |  |  |  |  | 15743 | 2011-2012 | [NAME] | Withdraw 1 | 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 | Productive withdrawal | 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 | Seamless withdrawal | 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 | Seamless withdrawal | 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.