Calculating the notice period

[Oak Ville]

Hi All,
I need to calculate the notice period based on province and the length of service in cell B22. Does anyone know how to do that?
Please see attached.
thanks

 

[Somendra Misra]

Hi Oak,

Can you make your dropdown's work by making static lists, as they are not working in sample file because of external links.

Regards,

 

[Oak Ville]

sorry, here you go.

 

[Somendra Misra]

Try Below formula in B22:

=INDEX(Legislation!$G$4:$G$86,SMALL(IF((Calculator!$B$18>=Legislation!$E$4:$E$86)*(Calculator!$B$18<=Legislation!$F$4:$F$86),IF(LOOKUP(ROW(Legislation!$A$4:$A$86),ROW(Legislation!$A$4:$A$86)/(Legislation!$A$4:$A$86<>""),Legislation!$A$4:$A$86)=VLOOKUP($B$5,Sheet3!$D$1:$E$14,2,0),ROW(Legislation!$G$4:$G$86)-ROW(Legislation!$G$4)+1)),1))

Note this is an array formula so must be entered with Ctrl+Shift+Enter. Also see the file for VLOOKUP function of getting province name from abbr. on Sheet 3

Regards,

 

[Oak Ville]

Amazing. thank you, I wouldn't be able to figure this out on my own.

 

[Somendra Misra]

Thanks for the feedback & Welcome back anytime!!

Regards,