Excel Lookup with Multiple (Dynamic) Criteria

[wakerider017]

Hey guys. Not really sure how to tackle this one. Looking to populate the highlighted cells (F15, F16, G15, G16) with the correct CMG code based on 2 rules. The rules are found in the upper table in columns D:H. Each rule is split into 3 columns, the metric specified, the operator and the value. Most rules check motor score, while some check age and in some cases there is only 1 rule. After the CMG is filled in cells F15 and F16 I will need to then use that code to lookup the weight in columns I:L (which should be a fairly simple task).

Any ideas? Thanks!

Note, the highlighted cells should be the following values:
F15 = 202
F16 = 105
G15 = 1.2534
G16 = 1.9391

 

[wakerider017]

Both these array formulas seem to work (ctrl+shift+enter):

=MATCH("TRUETRUETRUE",($A$2:$A$12=B15)&IF(LEN($C$2:$C$12)>0,CHOOSE(MATCH($C$2:$C$12,$C$14:$D$14,0),CHOOSE(MATCH($D$2:$D$12,{">",">=","<","<="},0),$C15>$E$2:$E$12,$C15>=$E$2:$E$12,$C15<$E$2:$E$12,$C15<=$E$2:$E$12),CHOOSE(MATCH($D$2:$D$12,{">",">=","<","<="},0),$D15>$E$2:$E$12,$D15>=$E$2:$E$12,$D15<$E$2:$E$12,$D15<=$E$2:$E$12)),TRUE)&IF(LEN($F$2:$F$12)>0,CHOOSE(MATCH($F$2:$F$12,$C$14:$D$14,0),CHOOSE(MATCH($G$2:$G$12,{">",">=","<","<="},0),$C15>$H$2:$H$12,$C15>=$H$2:$H$12,$C15<$H$2:$H$12,$C15<=$H$2:$H$12),CHOOSE(MATCH($G$2:$G$12,{">",">=","<","<="},0),$D15>$H$2:$H$12,$D15>=$H$2:$H$12,$D15<$H$2:$H$12,$D15<=$H$2:$H$12)),TRUE),0)

=MATCH(4,IF($C$2:$C$12=$C$14,COUNTIFS(B15,A$2:A$12,C15,D$2:D$12&E$2:E$12)>0,1)+IF($F$2:$F$12=$C$14,COUNTIFS(B15,A$2:A$12,C15,G$2:G$12&H$2:H$12)>0,1)+IF($C$2:$C$12=$D$14,COUNTIFS(B15,A$2:A$12,D15,D$2:D$12&E$2:E$12)>0,1)+IF($F$2:$F$12=$D$14,COUNTIFS(B15,A$2:A$12,D15,G$2:G$12&H$2:H$12)>0,1),0)

Credit to jblood94