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-
[TABLE="width: 593"]
<tbody>[TR]
[TD]A[/TD]
[TD]B [/TD]
[TD] C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]CIS Key [/TD]
[TD]Document Created Date[/TD]
[TD]Client Name[/TD]
[TD]Planner Name[/TD]
[TD]Case ID[/TD]
[TD]Task when Document was Created [/TD]
[/TR]
[TR]
[TD="align: right"]1111111[/TD]
[TD="align: right"] 8/04/2018[/TD]
[TD] Client 1[/TD]
[TD]Planner 1[/TD]
[TD="align: right"]23565454[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2222222[/TD]
[TD="align: right"]8/04/2018[/TD]
[TD] Client 2[/TD]
[TD]Planner 2[/TD]
[TD="align: right"]46412125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3333333[/TD]
[TD="align: right"]8/04/2018[/TD]
[TD] Client 3[/TD]
[TD]Planner 3[/TD]
[TD="align: right"]56467674[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4444444[/TD]
[TD="align: right"]9/04/2018[/TD]
[TD] Client 4[/TD]
[TD]Planner 4[/TD]
[TD="align: right"]78912134[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]


Sheet2-
[TABLE="width: 637"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Case ID[/TD]
[TD]Case Status[/TD]
[TD]Task[/TD]
[TD]TASKID[/TD]
[TD]Task Start date[/TD]
[TD]Task end date[/TD]
[/TR]
[TR]
[TD="align: right"]23565454[/TD]
[TD]Active[/TD]
[TD]Refine and Finalise Advice Document[/TD]
[TD="align: right"]21577829[/TD]
[TD="align: right"]8/04/2018[/TD]
[TD="align: right"]16/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]46412125[/TD]
[TD]Active[/TD]
[TD]Perform Quality Assurance[/TD]
[TD="align: right"]21577844[/TD]
[TD="align: right"]16/04/2018[/TD]
[TD="align: right"]16/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]56467674[/TD]
[TD]Active[/TD]
[TD]Verify Implementation[/TD]
[TD="align: right"]21577883[/TD]
[TD="align: right"]8/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]78912134[/TD]
[TD]Active[/TD]
[TD]Confirm Advice Requirements[/TD]
[TD="align: right"]21577935[/TD]
[TD="align: right"]16/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23565454[/TD]
[TD]Active[/TD]
[TD]Confirm Advice Requirements[/TD]
[TD="align: right"]21577942[/TD]
[TD="align: right"]16/04/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]46412125[/TD]
[TD]Active[/TD]
[TD]Perform Collaboration Call[/TD]
[TD="align: right"]21577989[/TD]
[TD="align: right"]8/04/2018[/TD]
[TD="align: right"]16/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]56467674[/TD]
[TD]Active[/TD]
[TD]Finalise Advice Document[/TD]
[TD="align: right"]21578007[/TD]
[TD="align: right"]16/04/2018[/TD]
[TD="align: right"]16/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]78912134[/TD]
[TD]Active[/TD]
[TD]Collect Customer Financials[/TD]
[TD="align: right"]21578000[/TD]
[TD="align: right"]8/04/2018[/TD]
[TD="align: right"]16/04/2018[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]


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-
[TABLE="class: cms_table, width: 593"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD] C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]CIS Key
[/TD]
[TD]Document Created Date
[/TD]
[TD]Client Name
[/TD]
[TD]Planner Name
[/TD]
[TD]Case ID
[/TD]
[TD]Task when Document was Created
[/TD]
[/TR]
[TR]
[TD="align: right"]1111111
[/TD]
[TD="align: right"] 8/04/2018
[/TD]
[TD] Client 1
[/TD]
[TD]Planner 1
[/TD]
[TD="align: right"]23565454
[/TD]
[TD]XXXX[/TD]
[/TR]
[TR]
[TD="align: right"]2222222
[/TD]
[TD="align: right"]8/04/2018
[/TD]
[TD] Client 2
[/TD]
[TD]Planner 2
[/TD]
[TD="align: right"]46412125
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3333333
[/TD]
[TD="align: right"]8/04/2018
[/TD]
[TD] Client 3
[/TD]
[TD]Planner 3
[/TD]
[TD="align: right"]56467674
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4444444
[/TD]
[TD="align: right"]9/04/2018
[/TD]
[TD] Client 4
[/TD]
[TD]Planner 4
[/TD]
[TD="align: right"]78912134
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Sheet2-
[TABLE="class: cms_table, width: 637"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]Case ID
[/TD]
[TD]Case Status
[/TD]
[TD]Task
[/TD]
[TD]TASKID
[/TD]
[TD]Task Start date
[/TD]
[TD]Task end date
[/TD]
[/TR]
[TR]
[TD="align: right"]23565454
[/TD]
[TD]Active
[/TD]
[TD]Refine and Finalise Advice Document
[/TD]
[TD="align: right"]21577829
[/TD]
[TD="align: right"]04/04/2018
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]23566454
[/TD]
[TD]Active
[/TD]
[TD]Perform Quality Assurance
[/TD]
[TD="align: right"]21577844
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]23566454
[/TD]
[TD]Active
[/TD]
[TD]Verify Implementation
[/TD]
[TD="align: right"]21577883
[/TD]
[TD="align: right"]02/04/2018
[/TD]
[TD] 04/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]78912134
[/TD]
[TD]Active
[/TD]
[TD]Confirm Advice Requirements
[/TD]
[TD="align: right"]21577935
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23565454
[/TD]
[TD]Active
[/TD]
[TD]Confirm Advice Requirements
[/TD]
[TD="align: right"]21577942
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]46412125
[/TD]
[TD]Active
[/TD]
[TD]Perform Collaboration Call
[/TD]
[TD="align: right"]21577989
[/TD]
[TD="align: right"]8/04/2018
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]56467674
[/TD]
[TD]Active
[/TD]
[TD]Finalise Advice Document
[/TD]
[TD="align: right"]21578007
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[/TR]
[TR]
[TD="align: right"]78912134
[/TD]
[TD]Active
[/TD]
[TD]Collect Customer Financials
[/TD]
[TD="align: right"]21578000
[/TD]
[TD="align: right"]8/04/2018
[/TD]
[TD="align: right"]16/04/2018
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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))