Help! Im hopelessly lost in huge nested IF formula and i can't get it to work properly..

[coban]

Ref. screenshot: Imgur: The most awesome images on the Internet

Here is the formula in question:

=IF(B13="";"";IF(C13="Online";IF(B13="Denmark";(DATE(D13;1;-2)-WEEKDAY(DATE(D13;1;3))+((F13-$G$2)*7))+1;(DATE(D13;1;-2)-WEEKDAY(DATE(D13;1;3))+((F13-$G$2)*7))+1);IF(B13="Denmark";(DATE(D13;1;-2)-WEEKDAY(DATE(D13;1;3))+((F13-$G$3)*7))+1;(DATE(D13;1;-2)-WEEKDAY(DATE(D13;1;3))+((F13-$G$4)*7))+1)))

Let me try to how it works, and how it should work:

- I want our marketing department to insert the blue fields "marketing", and then i want the rows in column "K" ("forecast from" - "forecast to", ref. formula above) to calculate a certain date: Tuesday of the "Weeknumber" column. Ref# screenshot/imgur, eg: If "Online" and "Weeknumber" = 6, then it should display: 2/2-16, Tuesday week 5. The rest of the dates are just minor changes from this date.

- Currently it works like this: Based on "sell-from" - "sell- to" dates, the "weeknumber" column ("F") get the proper week then adds "lead time" based on static values eg. "denmark", "online".

- I want it to find Tuesday of the "weeknumber" cell, but based on the matrix -- not the rubbish static values. If it is an online activity it should always grab the "Online" leadtime. But if it is "Physical", then it should grab the leadtime based on the country/"denmark, norway, finland, sweden".​

It worked until some C-level executives started digging their nose in my spreadsheet, and now i have to clean up the mess i made since they want to distribute it around.

 

[steve the fish]

You could use a formula like this to find your leadtimes:

=IF(B13="","",INDEX($E$2:$G$6,MATCH("*"&B13&"*",E2:E5,0),3))

 

[ctd2015]

E2:E5 will be absolute references i.e. $E$2:$E$5

 

[coban]

You could use a formula like this to find your leadtimes:

=IF(B13="","",INDEX($E$2:$G$6,MATCH("*"&B13&"*",E2:E5,0),3))

THANKS! I was just so hopelessly lost into "IF" that i couldn't think straight.