Advanced array function / Nested IF help required

[nikhil0311]

Based ob below 5 criteria, I want the output in Column G i.e. in Column "Final Comment"


[TABLE="width: 1120"]
<colgroup><col style="width:120pt" span="7" width="160"> </colgroup><tbody>[TR]
[TD="class: xl69, width: 160"]Qualifying EU LDD
[/TD]
[TD="class: xl66, width: 160"]Migration date/(Allocated date)[/TD]
[TD="class: xl67, width: 160"]KYC Next Renewal Date[/TD]
[TD="class: xl69, width: 160"]Date Equivalency Required By[/TD]
[TD="class: xl68, width: 160"]KYC Risk Rating[/TD]
[TD="class: xl68, width: 160"]CLE PEP?[/TD]
[TD="class: xl70, width: 160"]Final Comment[/TD]
[/TR]
[TR]
[TD="class: xl71"]Yes[/TD]
[TD="class: xl72"]1-Mar-20[/TD]
[TD="class: xl72"]5-Jul-19[/TD]
[TD="class: xl72"]1-Oct-19[/TD]
[TD="class: xl71"]Low[/TD]
[TD="class: xl71"]PEP [/TD]
[TD="class: xl73"] [/TD]
[/TR]
[TR]
[TD="class: xl71"]No[/TD]
[TD="class: xl72"]12-Feb-19[/TD]
[TD="class: xl72"]1-Jan-19[/TD]
[TD="class: xl72"]12-Sep-18[/TD]
[TD="class: xl71"]High[/TD]
[TD="class: xl71"]No[/TD]
[TD="class: xl73"] [/TD]
[/TR]
[TR]
[TD="class: xl71"]No[/TD]
[TD="class: xl72"]18-Jun-18[/TD]
[TD="class: xl72"]1-Jan-17[/TD]
[TD="class: xl72"]18-Jan-18[/TD]
[TD="class: xl71"]High[/TD]
[TD="class: xl71"]No[/TD]
[TD="class: xl73"] [/TD]
[/TR]
[TR]
[TD="class: xl71"]No[/TD]
[TD="class: xl72"]14-Nov-19[/TD]
[TD="class: xl72"]3-Jun-20[/TD]
[TD="class: xl72"]14-Jun-19[/TD]
[TD="class: xl71"]High[/TD]
[TD="class: xl71"]No[/TD]
[TD="class: xl73"] [/TD]
[/TR]
[TR]
[TD="class: xl71"]No[/TD]
[TD="class: xl72"]3-Jun-20[/TD]
[TD="class: xl72"]18-Jun-18[/TD]
[TD="class: xl72"]3-Jan-20[/TD]
[TD="class: xl71"]Low[/TD]
[TD="class: xl71"]PEP [/TD]
[TD="class: xl73"] [/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 366"]
<colgroup></colgroup><tbody>[TR]
[TD]Criteria 1
[/TD]
[/TR]
[TR]
[TD]If Qualifying EU LDD = YES
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]KYC Risk Rating = Low or Medium
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]CLE PEP = No
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]KYC Next Renewal date>=Today
[/TD]
[/TR]
[TR]
[TD]THEN "Ready To Migrate"
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]Criteria 2
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]If Qualifying EU LDD = No
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]Date Equivalency Required By > KYC Next Renewal date
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]KYC Risk Rating = Low or Medium
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]CLE PEP = No
[/TD]
[/TR]
[TR]
[TD]THEN "2 update to UK standard required at next schedule renewal"
[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]Criteria 3
[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]If Qualifying EU LDD = No
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]Date Equivalency Required By < KYC Next Renewal date
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]KYC Risk Rating = Low or Medium
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]CLE PEP = No
[/TD]
[/TR]
[TR]
[TD]THEN "3 update to UK standard required - remediation"
[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]Criteria 4
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]If Qualifying EU LDD = Yes or No
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]Date Equivalency Required By > KYC Next Renewal date
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]KYC Risk Rating = High
[/TD]
[/TR]
[TR]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]CLE PEP = PEP
[/TD]
[/TR]
[TR]
[TD]THEN "4 update to target location scheduled renewal"
[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]Criteria 5
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]If Qualifying EU LDD = Yes or No
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]Date Equivalency Required By < KYC Next Renewal date
[/TD]
[/TR]
[TR]
[TD]AND
[/TD]
[/TR]
[TR]
[TD]KYC Risk Rating = High
[/TD]
[/TR]
[TR]
[TD]OR
[/TD]
[/TR]
[TR]
[TD]CLE PEP = PEP
[/TD]
[/TR]
[TR]
[TD]THEN "5 update to target location - Remediation"
[/TD]
[/TR]
</tbody>[/TABLE]

let me know if you have any questions!

 

[ranman256]

USAGE: =getResult()

I'm not sure on your: 'OR Pep="PEP"' logic.
adjust at will.

Function getResult()
Dim vEU, vMigDte, vRenDte, vEQDte, vKycRisk, vPEP
Const kNO = "No"
Const kYES = "Yes"
Const kLO = "Low"
Const kMED = "Medium"
Const kHI = "High"
Const kPEP = "PEP"

 'Range("A1").Select
vEU = ActiveCell.Offset(0, -6).Value
vMigDte = ActiveCell.Offset(0, -5).Value
vRenDte = ActiveCell.Offset(0, -4).Value
vEQDte = ActiveCell.Offset(0, -3).Value
vKycRisk = ActiveCell.Offset(0, -2).Value
vPEP = ActiveCell.Offset(0, -1).Value


Select Case True
    '1
  Case vEU = kYES And (vKycRisk = kLO Or vKycRisk = kMED) And vPEP = kNO And vRenDte >= Date
      getResult = "Ready to Migrate"
    '2
  Case vEU = kNO And (vKycRisk = kLO Or vKycRisk = kMED) And vPEP = kNO And vEQDte > vRenDte
      getResult = "2 update to UK standard required at next schedule renewal"
    '3
  Case vEU = kNO And (vKycRisk = kLO Or vKycRisk = kMED) And vPEP = kNO And vEQDte < vRenDte
      getResult = "3 update to UK standard required - remediation"
    '4
  Case (vKycRisk = kHI) And vEQDte > vRenDte Or vPEP = kPEP
      getResult = "4 update to target location scheduled renewal"
      
    '5
  Case (vKycRisk = kHI) And vEQDte < vRenDte Or vPEP = kPEP
      getResult = "5 update to target location - Remediation"
    
  Case Else
    getResult = ""
End Select
End Function