Spreadsheet: 682225d1591968372-if-statement-with-search-and-match-dateexample
Sheet: Sheet1

Original Date | Status | Date1 | Date2 | Date3 | [NAME]
2020-06-08 00:00:00 | RECONCILED | 2020-06-08 00:00:00 | 2020-06-08 00:00:00 | 2020-06-08 00:00:00 | 2020-06-08 00:00:00
2020-06-08 00:00:00 | RECONCILED | 2020-06-08 00:00:00 | 2020-06-08 00:00:00 | 2020-06-08 00:00:00 | 2020-06-08 00:00:00
2020-06-05 00:00:00 | RECONCILED | 2020-06-05 00:00:00 | 2020-06-05 00:00:00 | 2020-06-05 00:00:00 | 2020-06-05 00:00:00
2020-06-04 00:00:00 | False | False | False | False | False
2020-06-04 00:00:00 | CHANGE | 2020-05-28 | 2020-05-29 | 2020-05-28 | 2020-05-29
2020-06-04 00:00:00 | CHANGE | 2020-05-29 | 2020-06-01 | 2020-05-29 | 2020-06-01
2020-06-04 00:00:00 | CHANGE | 2020-06-02 | 2020-06-03 | 2020-06-02 | 2020-06-03
2020-06-04 00:00:00 | CHANGE | 2020-06-03 | 2020-06-04 | 2020-06-03 | 2020-06-04 | [NAME] explanation for column E
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-05-28 | 2020-06-04 | IF B10 = "RECONCILED" & B9= "RECONCILED" use the date in A10. IF B10 = "RECONCILED" & B9="CHANGE", find the last cell showing "CHANGE" above that and use the date in column C of the same row. If nothing is true, the result can be "FALSE"
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | [NAME] for column F
2020-06-04 00:00:00 | False | False | False | False | False | IF B10 = "RECONCILED" & B9= "RECONCILED" use the date in A10. IF B10 = "RECONCILED" & B9="CHANGE", use the date in D9. If nothing is true, the result can be "FALSE"
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-05 00:00:00 | False | False | False | False | False
2020-06-05 00:00:00 | CHANGE | 2020-06-04 | 2020-06-05 | 2020-06-04 | 2020-06-05
2020-06-05 00:00:00 | RECONCILED | 2020-06-05 00:00:00 |  | 2020-06-05 00:00:00
Original Date | Status | Date1 | Date2
2020-06-08 00:00:00 | RECONCILED | 2020-06-08 00:00:00 | 2020-06-08 00:00:00
2020-06-08 00:00:00 | RECONCILED | 2020-06-08 00:00:00 | 2020-06-08 00:00:00
2020-06-05 00:00:00 | RECONCILED | 2020-06-05 00:00:00 | 2020-06-05 00:00:00
2020-06-04 00:00:00 | False | False | False
2020-06-04 00:00:00 | CHANGE | 2020-05-28 | 2020-05-29
2020-06-04 00:00:00 | CHANGE | 2020-05-29 | 2020-06-01
2020-06-04 00:00:00 | CHANGE | 2020-06-02 | 2020-06-03
2020-06-04 00:00:00 | CHANGE | 2020-06-03 | 2020-06-04
2020-06-04 00:00:00 | RECONCILED | 2020-05-28 | 2020-06-04
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | False | False | False
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-04 00:00:00 | RECONCILED | 2020-06-04 00:00:00 | 2020-06-04 00:00:00
2020-06-05 00:00:00 | False | False | False
2020-06-05 00:00:00 | CHANGE | 2020-06-04 | 2020-06-05
2020-06-05 00:00:00 | RECONCILED | 2020-06-04 | 2020-06-05