Formula with Multiple argumentsacross two worksheets

[PJB93]

Hi All,

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.

 

[Aladin Akyurek]

In F2 of Sheet1, control+shift+enter, not just enter, and copy down:

=INDEX(Sheet2!$C$2:$C$9,MATCH($E2,IF(Sheet2!$E$2:$E$9>=$B2,IF($B2<=IF(ISNUMBER(Sheet2!$F$2:$F$9),Sheet2!$F$2:$F$9,TODAY()),Sheet2!$A$2:$A$9)),0))

 

[PJB93]

Hi Aladin,

Unfortunately that did not work, it pulls a task from sheet2 but not the correct one

 

[Aladin Akyurek]

Hi Aladin,

Unfortunately that did not work, it pulls a task from sheet2 but not the correct one

Care to post the desired results that you want to see in column of Sheet1 for the data you have posted?

 

[PJB93]

I have changed my example slightly to better display what I am trying to achieve, using case ID 23565454 as my example in column F where the X's are I would expect that the 'Refine and Finalise Advice Document' task would appear here because the case Id on column E of sheet1 matches with the case ID in column A of sheet2 and the document created date in column B of sheet1 falls with the start and end date of that task on sheet2 (E & F).

So there are three tasks on sheet 2 that have a matching case ID but only one of those (refine and finalise) has a start and end date that is less than/equal to or greater than/equal to the document created date on sheet 1.
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	XXXX
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>

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	04/04/2018	16/04/2018
23566454	Active	Perform Quality Assurance	21577844	16/04/2018	16/04/2018
23566454	Active	Verify Implementation	21577883	02/04/2018	04/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>

 

[Aladin Akyurek]

In F2 of Sheet1 control+shift+enter, not just enter, and copy down:

=INDEX(Sheet2!$C$2:$C$9,MATCH($E2,IF($B$2>=Sheet2!$E$2:$E$9,IF($B2<=IF(ISNUMBER(Sheet2!$F$2:$F$9),Sheet2!$F$2:$F$9,TODAY()),Sheet2!$A$2:$A$9)),0))