Complex Index match or sumproduct?

[Melimob]

Hi

I'm stuck. I have a table where I want to read the dates depending on country and row type.

THEN - populate this on another table under the relevant date with a code.

So here's my table I am reading from (sorry I can't paste it as a table for some reason?):

[TABLE="width: 483"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Timeline[/TD]
[TD]Column1[/TD]
[TD]ARGENTINA[/TD]
[TD]BRAZIL[/TD]
[/TR]
[TR]
[TD]Imp Type[/TD]
[TD] [/TD]
[TD]IN[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]Client Notification Date[/TD]
[TD]START[/TD]
[TD]10-Jun-19[/TD]
[TD]17-Jun-19[/TD]
[/TR]
[TR]
[TD]Client Involvement Timeline[/TD]
[TD]Weeks[/TD]
[TD]14[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Launch Date[/TD]
[TD]GO LIVE[/TD]
[TD]02-Sep-19[/TD]
[TD]09-Sep-19[/TD]
[/TR]
[TR]
[TD]PLANNING[/TD]
[TD]Wk 1 - 4[/TD]
[TD]From 10-Jun-19 To 08-Jul-19[/TD]
[TD]From 17-Jun-19 To 15-Jul-19[/TD]
[/TR]
[TR]
[TD]IMPLEMENTATION[/TD]
[TD]Wk 4 - 11[/TD]
[TD]From 08-Jul-19 To 26-Aug-19[/TD]
[TD]From 15-Jul-19 To 02-Sep-19[/TD]
[/TR]
[TR]
[TD]OBT UAT Testing[/TD]
[TD]Wk 7 - 8[/TD]
[TD]From 29-Jul-19 To 05-Aug-19[/TD]
[TD]From 05-Aug-19 To 12-Aug-19[/TD]
[/TR]
[TR]
[TD]Communication Phase 2 & 3[/TD]
[TD]Wk 9 - 12[/TD]
[TD]From 12-Aug-19 To 02-Sep-19[/TD]
[TD]From 19-Aug-19 To 09-Sep-19[/TD]
[/TR]
[TR]
[TD]POST-IMPLEMENTATION[/TD]
[TD]Wk 12 - 14[/TD]
[TD]From 02-Sep-19 To 16-Sep-19[/TD]
[TD]From 09-Sep-19 To 23-Sep-19[/TD]
[/TR]
</tbody>[/TABLE]


Then I want it to appear here as shown (then I will apply conditional formatting to colour code the different parts of the plan):

[TABLE="width: 1206"]
<colgroup><col><col><col><col><col span="2"><col span="3"><col span="3"><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]10-Jun[/TD]
[TD]17-Jun[/TD]
[TD]24-Jun[/TD]
[TD]01-Jul[/TD]
[TD]08-Jul[/TD]
[TD]15-Jul[/TD]
[TD]22-Jul[/TD]
[TD]29-Jul[/TD]
[TD]05-Aug[/TD]
[TD]12-Aug[/TD]
[TD]19-Aug[/TD]
[TD]26-Aug[/TD]
[TD]02-Sep[/TD]
[TD]09-Sep[/TD]
[TD]16-Sep[/TD]
[TD]23-Sep[/TD]
[TD]30-Sep[/TD]
[/TR]
[TR]
[TD]ARGENTINA[/TD]
[TD]START[/TD]
[TD]PLAN[/TD]
[TD]PLAN[/TD]
[TD]PLAN[/TD]
[TD]PLAN[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]GO[/TD]
[TD]POST[/TD]
[TD]POST[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BRAZIL[/TD]
[TD] [/TD]
[TD]START[/TD]
[TD]PLAN[/TD]
[TD]PLAN[/TD]
[TD]PLAN[/TD]
[TD]PLAN[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]IMP[/TD]
[TD]GO[/TD]
[TD]POST[/TD]
[TD]POST[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I think I may need this as a helper table and lookup but since I have so many countries I would rather try and read from source.
I've played around with INDEX MATCH but need it to find the week and write under it the code.

Any help greatly appreciated!
thank you!

 

[Melimob]

OK so I've made a start but this works for the first row and then gives wrong results for following columns?
I can't understand why as I fixed the range and columns to drag across?

=IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],Table5[@[ARGENTINA]:[VIETNAM]],Table2[[#Headers],[10-Jun]])>0,"START","")

this is what is in my second column
=IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],Table5[@[ARGENTINA]:[VIETNAM]],Table2[[#Headers],[17-Jun]])>0,"START","")

This is what I'm getting which is correct for Argentina but Brazil should have START under 17-Jun?
Also another country has a start date of 2-Sep but it's appearing as START using above formula under 16-Sep? I can't figure out why?

[TABLE="width: 249"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]10-Jun[/TD]
[TD]17-Jun[/TD]
[/TR]
[TR]
[TD]ARGENTINA[/TD]
[TD]START[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BRAZIL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

many thanks for any advice!

 

[Melimob]

Ok so I solved the previous issue and here's my formula:

=IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],'Client Engagement Transition'!$D$4:$Z$4,Table2[[#Headers],[10-Jun]])>0,"START",IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],'Client Engagement Transition'!$D$6:$Z$6,Table2[[#Headers],[10-Jun]])>0,"GO",IF(COUNTIFS(Table5[[#Headers],[ARGENTINA]:[VIETNAM]],Table2[@[Country]:[Country]],'Client Engagement Transition'!$D$7:$Z$7,Table2[[#Headers],[10-Jun]])>0,"PLAN","")))

This works for 'START', 'GO' however 'PLAN' date is contained within text.

E.g.: [TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl6457, width: 130"]From 10-Jun-19 To 08-Jul-19[/TD]
[/TR]
</tbody>[/TABLE]

So it's not picking it up. I'm thinking to is number search but since I want to return all the weeks within 10-jun to 8-jul as 'PLAN' , perhaps it's better to say find the start date and mark all 4 following columns as 'PLAN'?

can someone help with this piece? many thanks