Index correct column based on multiple row criteria

[jsabin]

I need to index the correct corresponding column based on the results of multiple row match criteria. In sample one the Incremental Value should reference cell G13 and sample two should reference AD12. Also, there could be up to 70K of these samples referencing the same data so formula performance could become an issue too.

Here is a list of the different row match criteria:
1) criteria: =B4, Criteria Range: G4:AE4
2) criteria: =B5, Criteria Range: G6:AE6
3) criteria: >=B3, Criteria Range: G7:AE7
4) criteria: <=B3, Criteria Range: G8:AE8

 

[bosco_yip]

How to find the result G13 in the range of G11:G20 and AD12 in the range of AD11:AD13 ? Could you do the manual calculation.

Regards
Bosco

 

[jsabin]

I think that would be okay. I also have flexibility in the layout of the rules if that helps.

 

[bosco_yip]

Perhaps,

In C6 enter formula and copy to C15 :

=AGGREGATE(15,6,INDEX($G$12:$AE$27,,SUMPRODUCT((COLUMN($G$1:$AE$1)-COLUMN($F$1))*((B3>=$G$7:$AE$7)*(B3<=$G$8:$AE$8))*($G$4:$AE$4=B4)*($G$6:$AE$6=B5)))/(INDEX($G$12:$AE$27,,SUMPRODUCT((COLUMN($G$1:$AE$1)-COLUMN($F$1))*((B3>=$G$7:$AE$7)*(B3<=$G$8:$AE$8))*($G$4:$AE$4=B4)*($G$6:$AE$6=B5)))>=MOD(B3,1)),1)

Regards
Bosco

 

[jsabin]

This works beautifully. The only problem I found is when I enter a whole number. For example, if I change the number in the first sample to 3 the result is 0 instead of .19.
Thoughts?

 

[bosco_yip]

This works beautifully. The only problem I found is when I enter a whole number. For example, if I change the number in the first sample to 3 the result is 0 instead of .19.
Thoughts?

Then..........

Just changed the formula to :

=AGGREGATE(15,6,INDEX($G$12:$AE$27,,SUMPRODUCT((COLUMN($G$1:$AE$1)-COLUMN($F$1))*((B3>=$G$7:$AE$7)*(B3<=$G$8:$AE$8))*($G$4:$AE$4=B4)*($G$6:$AE$6=B5)))/(INDEX($G$12:$AE$27,,SUMPRODUCT((COLUMN($G$1:$AE$1)-COLUMN($F$1))*((B3>=$G$7:$AE$7)*(B3<=$G$8:$AE$8))*($G$4:$AE$4=B4)*($G$6:$AE$6=B5)))>=IF(MOD(B3,1),MOD(B3,1),0.001)),1)

Regards
Bosco

 

[jsabin]

It works flawlessly!! Thank you so much for your help Bosco!!