Hi All,
I am after some assistance with writing a formula.
This is the data I have to work with:
Sheet1-
<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>
Sheet2-
<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>
This is what I would like to achieve:
In column F of sheet1 - If the start date in column B of Sheet1 falls either on or between the start and end date in columns E & F of Sheet2 AND if the case ID in column E of Sheet1 matches with the case ID in column A of sheet2 then populate column F of Sheet1 with column C of Sheet2.
This is what I have done so far: =INDEX('Sheet2'!C:C,MATCH('Sheet1'!E2,'Sheet2'!A:C,3,FALSE))
I am not sure how to incorporate the date argument in the above, any ideas would be much appreciated.
Thank you.
I am after some assistance with writing a formula.
This is the data I have to work with:
Sheet1-
A | B | C | D | E | F |
CIS Key | Document Created Date | Client Name | Planner Name | Case ID | Task when Document was Created |
1111111 | 8/04/2018 | Client 1 | Planner 1 | 23565454 | |
2222222 | 8/04/2018 | Client 2 | Planner 2 | 46412125 | |
3333333 | 8/04/2018 | Client 3 | Planner 3 | 56467674 | |
4444444 | 9/04/2018 | Client 4 | Planner 4 | 78912134 |
<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>
Sheet2-
A | B | C | D | E | F |
Case ID | Case Status | Task | TASKID | Task Start date | Task end date |
23565454 | Active | Refine and Finalise Advice Document | 21577829 | 8/04/2018 | 16/04/2018 |
46412125 | Active | Perform Quality Assurance | 21577844 | 16/04/2018 | 16/04/2018 |
56467674 | Active | Verify Implementation | 21577883 | 8/04/2018 | |
78912134 | Active | Confirm Advice Requirements | 21577935 | 16/04/2018 | |
23565454 | Active | Confirm Advice Requirements | 21577942 | 16/04/2018 | |
46412125 | Active | Perform Collaboration Call | 21577989 | 8/04/2018 | 16/04/2018 |
56467674 | Active | Finalise Advice Document | 21578007 | 16/04/2018 | 16/04/2018 |
78912134 | Active | Collect Customer Financials | 21578000 | 8/04/2018 | 16/04/2018 |
<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>
This is what I would like to achieve:
In column F of sheet1 - If the start date in column B of Sheet1 falls either on or between the start and end date in columns E & F of Sheet2 AND if the case ID in column E of Sheet1 matches with the case ID in column A of sheet2 then populate column F of Sheet1 with column C of Sheet2.
This is what I have done so far: =INDEX('Sheet2'!C:C,MATCH('Sheet1'!E2,'Sheet2'!A:C,3,FALSE))
I am not sure how to incorporate the date argument in the above, any ideas would be much appreciated.
Thank you.