Spreadsheet: 599854d1543336783-retrieving-another-text-string-based-on-multiple-criteria-formula-requires-update-program-changes-after-withdrawals-2018-11-26
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 |  | Reference Table
18028 | 2011-2012 | Advanced | remained | Total | Total | (did not return) |  |  | ID | Year | Program | Registration Status | Start Date | End Date
23974 | 2010-2011 |  |  |  |  |  |  |  | 18390 | 2015-2016 | Study Group | Withdraw 1 | 2015-08-31 00:00:00 | 2015-12-31 00:00:00
21086 | 2016-2017 | Shake |  |  | Cup |  |  |  | 18390 | 2015-2016 | Finished Product | Withdraw 1 | 2016-01-02 00:00:00 | 2016-04-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 | 2015-2016 | General | Withdraw 4 | 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
 |  |  |  |  |  |  |  |  | 18028 | 2010-2011 | Basic | Withdraw 3 | 2009-09-08 00:00:00 | 2011-04-30 00:00:00
 |  | Desired results lists all programs changed into after each withdrawal in the year specified (Column B). If the program does not change after a one of 4 types of withdrawals then the cell should return "remained". If there was no type of withdrawal in the year, then all desired cells should remain blank (""). All withdrawal types are treated the same (withdraw 1, withdraw 2, withdraw 3, and withdraw 4), they are only spelt differently.   Sometimes an ID can have a withdrawal and there is no program at a  (row) listed for that same ID at a later date. In this case, the desired result should read "(did not return)".   It is possible that an ID may have returned in the next year and changed programs. This program should still be recorded in the year earlier and should be recorded as the last program. (this scenario occurs with ID # 18390).  Note that 3 withdrawals is the maximum number of withdrawals that can occur in one year. |  |  |  |  |  |  | 18028 | 2011-2012 | Advanced | Withdraw 2 | 2011-05-01 00:00:00 | 2011-08-31 00:00:00
 |  |  |  |  |  |  |  |  | 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 | 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 | 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 | 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 | Withdraw 3 | 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.