Complex Index match or sumproduct?

Melimob

Active Member
Joined
Oct 16, 2011
Messages
395
Office Version
  1. 365
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?):

TimelineColumn1ARGENTINABRAZIL
Imp Type ININ
Client Notification DateSTART10-Jun-1917-Jun-19
Client Involvement TimelineWeeks1414
Launch DateGO LIVE02-Sep-1909-Sep-19
PLANNINGWk 1 - 4From 10-Jun-19 To 08-Jul-19From 17-Jun-19 To 15-Jul-19
IMPLEMENTATIONWk 4 - 11From 08-Jul-19 To 26-Aug-19From 15-Jul-19 To 02-Sep-19
OBT UAT TestingWk 7 - 8From 29-Jul-19 To 05-Aug-19From 05-Aug-19 To 12-Aug-19
Communication Phase 2 & 3Wk 9 - 12From 12-Aug-19 To 02-Sep-19From 19-Aug-19 To 09-Sep-19
POST-IMPLEMENTATIONWk 12 - 14From 02-Sep-19 To 16-Sep-19From 09-Sep-19 To 23-Sep-19

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


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

Country10-Jun17-Jun24-Jun01-Jul08-Jul15-Jul22-Jul29-Jul05-Aug12-Aug19-Aug26-Aug02-Sep09-Sep16-Sep23-Sep30-Sep
ARGENTINASTARTPLANPLANPLANPLANIMPIMPIMPIMPIMPIMPIMPGOPOSTPOST
BRAZIL STARTPLANPLANPLANPLANIMPIMPIMPIMPIMPIMPIMPGOPOSTPOST

<colgroup><col><col><col><col><col span="2"><col span="3"><col span="3"><col><col span="5"></colgroup><tbody>
</tbody>

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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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?

Country10-Jun17-Jun
ARGENTINASTART
BRAZIL

<colgroup><col><col><col></colgroup><tbody>
</tbody>

many thanks for any advice!
 
Upvote 0
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.:
From 10-Jun-19 To 08-Jul-19

<tbody>
</tbody>

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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top