Dear MrExcel members,
Thanks to the fabulous help of some of your members ( https://www.mrexcel.com/forum/excel...teria-great-than-post4879161.html#post4879161 ) I created the following SUMPRODUCT formula. The goal of the formula is to select results from a big look-up table based on four criteria: a CATEGORY (categorical), VALUE1 (numeric), VALUE2 (numeric), VALUE3 (numeric).
The look-up table looks as follows:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]CATEGORY[/TD]
[TD]VALUEFROM1[/TD]
[TD]VALUETO1[/TD]
[TD]VALUEFROM2[/TD]
[TD]VALUETO2[/TD]
[TD]VALUEFROM3[/TD]
[TD]VALUETO3[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]999[/TD]
[TD]0[/TD]
[TD]499[/TD]
[TD]100[/TD]
[TD]9999[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1000[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]499[/TD]
[TD]100[/TD]
[TD]9999[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]999[/TD]
[TD]500[/TD]
[TD]9999[/TD]
[TD]100[/TD]
[TD]9999[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1000[/TD]
[TD]9999[/TD]
[TD]500[/TD]
[TD]9999[/TD]
[TD]100[/TD]
[TD]9999[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[TD]999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1000[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0[/TD]
[TD]89[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]90[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0.1[/TD]
[/TR]
</tbody>[/TABLE]
The table that I'm trying to make, looks as follows:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Numerical criteria (VALUE1, VALUE2 and VALUE3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VALUE1[/TD]
[TD]55[/TD]
[TD]90[/TD]
[TD]30[/TD]
[TD]432[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]VALUE2[/TD]
[TD]800[/TD]
[TD]700[/TD]
[TD]300[/TD]
[TD]23[/TD]
[TD]457[/TD]
[/TR]
[TR]
[TD]VALUE3[/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD]0[/TD]
[TD]900[/TD]
[TD]754[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As a formula for the Result, I use the following (e.g. for the top-left result cell):
=SUMPRODUCT(MAX(
(LOOKUPTABLE[CATEGORY]=$A8)
*(LOOKUPTABLE[VALUEFROM1]<=IF(B$2="";LOOKUPTABLE[VALUEFROM1];B$2))
*(LOOKUPTABLE[VALUETO1]>=IF(B$2="";LOOKUPTABLE[VALUETO1];B$2))
*(LOOKUPTABLE[VALUEFROM2]<=IF(B$3="";LOOKUPTABLE[VALUEFROM2];B$3))
*(LOOKUPTABLE[VALUETO2]>=IF(B$3="";LOOKUPTABLE[VALUETO2];B$3))
*(LOOKUPTABLE[VALUEFROM3]<=IF(B$4="";LOOKUPTABLE[VALUEFROM3];B$4))
*(LOOKUPTABLE[VALUETO3]>=IF(B$4="";LOOKUPTABLE[VALUETO3];B$4))
*(LOOKUPTABLE[RESULT])))
(This formula accounts also for special cases in which VALUE1 might be empty)
This gives the following result:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Numerical criteria (VALUE1, VALUE2 and VALUE3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VALUE1[/TD]
[TD]55[/TD]
[TD]90[/TD]
[TD]30[/TD]
[TD]432[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]VALUE2[/TD]
[TD]800[/TD]
[TD]700[/TD]
[TD]300[/TD]
[TD]23[/TD]
[TD]457[/TD]
[/TR]
[TR]
[TD]VALUE3[/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD]0[/TD]
[TD]900[/TD]
[TD]754[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0.1[/TD]
[TD]0.1[/TD]
[TD]0.1[/TD]
[TD]0.4[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0.5[/TD]
[TD]0.5[/TD]
[TD]0.5[/TD]
[TD]0.5[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0.3[/TD]
[TD]0.1[/TD]
[TD]0.3[/TD]
[TD]0.1[/TD]
[TD]0.3[/TD]
[/TR]
</tbody>[/TABLE]
This works nicely, but once I add more rows to the result table (by adding categories D-P) and I add more columns to the table (some 40) and I add more rows to the look-up table (till 30,000), these formulas become slower. Therefore, I would like to write ONE array formula for the whole result table. If this isn't possible, then I would be happy if I could have ONE array formula per category.
I tried using the following array formula, but it doesn't work as I would like, as it returns N/A:
{=SUMPRODUCT(MAX(
(Table35[CATEGORY]=$A8:$A10)
*(Table35[VALUEFROM1]<=IF(B$2:F$2="";Table35[VALUEFROM1];B$2:F$2))
*(Table35[VALUETO1]>=IF(B$2:F$2="";Table35[VALUETO1];B$2:F$2))
*(Table35[VALUEFROM2]<=IF(B$3:F$3="";Table35[VALUEFROM2];B$3:F$3))
*(Table35[VALUETO2]>=IF(B$3:F$3="";Table35[VALUETO2];B$3:F$3))
*(Table35[VALUEFROM3]<=IF(B$4:F$4="";Table35[VALUEFROM3];B$4:F$4))
*(Table35[VALUETO3]>=IF(B$4:F$4="";Table35[VALUETO3];B$4:F$4))
*(Table35[RESULT])))}
Further, I tried the following array formula for each category, but it doesn't work, as it gives the same result for the whole row of the category:
{=SUMPRODUCT(MAX(
(LOOKUPTABLE[CATEGORY]=$A8)
*(LOOKUPTABLE[VALUEFROM1]<=IF(B$2:F$2="";LOOKUPTABLE[VALUEFROM1];B$2:F$2))
*(LOOKUPTABLE[VALUETO1]>=IF(B$2:F$2="";LOOKUPTABLE[VALUETO1];B$2:F$2))
*(LOOKUPTABLE[VALUEFROM2]<=IF(B$3:F$3="";LOOKUPTABLE[VALUEFROM2];B$3:F$3))
*(LOOKUPTABLE[VALUETO2]>=IF(B$3:F$3="";LOOKUPTABLE[VALUETO2];B$3:F$3))
*(LOOKUPTABLE[VALUEFROM3]<=IF(B$4:F$4="";LOOKUPTABLE[VALUEFROM3];B$4:F$4))
*(LOOKUPTABLE[VALUETO3]>=IF(B$4:F$4="";LOOKUPTABLE[VALUETO3];B$4:F$4))
*(LOOKUPTABLE[RESULT])))}
Do you have some ideas, how I could solve this problem?
Thanks a lot in advance for any suggestions!
Netraam
Thanks to the fabulous help of some of your members ( https://www.mrexcel.com/forum/excel...teria-great-than-post4879161.html#post4879161 ) I created the following SUMPRODUCT formula. The goal of the formula is to select results from a big look-up table based on four criteria: a CATEGORY (categorical), VALUE1 (numeric), VALUE2 (numeric), VALUE3 (numeric).
The look-up table looks as follows:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]CATEGORY[/TD]
[TD]VALUEFROM1[/TD]
[TD]VALUETO1[/TD]
[TD]VALUEFROM2[/TD]
[TD]VALUETO2[/TD]
[TD]VALUEFROM3[/TD]
[TD]VALUETO3[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]999[/TD]
[TD]0[/TD]
[TD]499[/TD]
[TD]100[/TD]
[TD]9999[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1000[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]499[/TD]
[TD]100[/TD]
[TD]9999[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]999[/TD]
[TD]500[/TD]
[TD]9999[/TD]
[TD]100[/TD]
[TD]9999[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1000[/TD]
[TD]9999[/TD]
[TD]500[/TD]
[TD]9999[/TD]
[TD]100[/TD]
[TD]9999[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]99[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0[/TD]
[TD]999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1000[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0[/TD]
[TD]89[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]90[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0[/TD]
[TD]9999[/TD]
[TD]0.1[/TD]
[/TR]
</tbody>[/TABLE]
The table that I'm trying to make, looks as follows:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Numerical criteria (VALUE1, VALUE2 and VALUE3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VALUE1[/TD]
[TD]55[/TD]
[TD]90[/TD]
[TD]30[/TD]
[TD]432[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]VALUE2[/TD]
[TD]800[/TD]
[TD]700[/TD]
[TD]300[/TD]
[TD]23[/TD]
[TD]457[/TD]
[/TR]
[TR]
[TD]VALUE3[/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD]0[/TD]
[TD]900[/TD]
[TD]754[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As a formula for the Result, I use the following (e.g. for the top-left result cell):
=SUMPRODUCT(MAX(
(LOOKUPTABLE[CATEGORY]=$A8)
*(LOOKUPTABLE[VALUEFROM1]<=IF(B$2="";LOOKUPTABLE[VALUEFROM1];B$2))
*(LOOKUPTABLE[VALUETO1]>=IF(B$2="";LOOKUPTABLE[VALUETO1];B$2))
*(LOOKUPTABLE[VALUEFROM2]<=IF(B$3="";LOOKUPTABLE[VALUEFROM2];B$3))
*(LOOKUPTABLE[VALUETO2]>=IF(B$3="";LOOKUPTABLE[VALUETO2];B$3))
*(LOOKUPTABLE[VALUEFROM3]<=IF(B$4="";LOOKUPTABLE[VALUEFROM3];B$4))
*(LOOKUPTABLE[VALUETO3]>=IF(B$4="";LOOKUPTABLE[VALUETO3];B$4))
*(LOOKUPTABLE[RESULT])))
(This formula accounts also for special cases in which VALUE1 might be empty)
This gives the following result:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Numerical criteria (VALUE1, VALUE2 and VALUE3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VALUE1[/TD]
[TD]55[/TD]
[TD]90[/TD]
[TD]30[/TD]
[TD]432[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]VALUE2[/TD]
[TD]800[/TD]
[TD]700[/TD]
[TD]300[/TD]
[TD]23[/TD]
[TD]457[/TD]
[/TR]
[TR]
[TD]VALUE3[/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD]0[/TD]
[TD]900[/TD]
[TD]754[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0.1[/TD]
[TD]0.1[/TD]
[TD]0.1[/TD]
[TD]0.4[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]0.5[/TD]
[TD]0.5[/TD]
[TD]0.5[/TD]
[TD]0.5[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0.3[/TD]
[TD]0.1[/TD]
[TD]0.3[/TD]
[TD]0.1[/TD]
[TD]0.3[/TD]
[/TR]
</tbody>[/TABLE]
This works nicely, but once I add more rows to the result table (by adding categories D-P) and I add more columns to the table (some 40) and I add more rows to the look-up table (till 30,000), these formulas become slower. Therefore, I would like to write ONE array formula for the whole result table. If this isn't possible, then I would be happy if I could have ONE array formula per category.
I tried using the following array formula, but it doesn't work as I would like, as it returns N/A:
{=SUMPRODUCT(MAX(
(Table35[CATEGORY]=$A8:$A10)
*(Table35[VALUEFROM1]<=IF(B$2:F$2="";Table35[VALUEFROM1];B$2:F$2))
*(Table35[VALUETO1]>=IF(B$2:F$2="";Table35[VALUETO1];B$2:F$2))
*(Table35[VALUEFROM2]<=IF(B$3:F$3="";Table35[VALUEFROM2];B$3:F$3))
*(Table35[VALUETO2]>=IF(B$3:F$3="";Table35[VALUETO2];B$3:F$3))
*(Table35[VALUEFROM3]<=IF(B$4:F$4="";Table35[VALUEFROM3];B$4:F$4))
*(Table35[VALUETO3]>=IF(B$4:F$4="";Table35[VALUETO3];B$4:F$4))
*(Table35[RESULT])))}
Further, I tried the following array formula for each category, but it doesn't work, as it gives the same result for the whole row of the category:
{=SUMPRODUCT(MAX(
(LOOKUPTABLE[CATEGORY]=$A8)
*(LOOKUPTABLE[VALUEFROM1]<=IF(B$2:F$2="";LOOKUPTABLE[VALUEFROM1];B$2:F$2))
*(LOOKUPTABLE[VALUETO1]>=IF(B$2:F$2="";LOOKUPTABLE[VALUETO1];B$2:F$2))
*(LOOKUPTABLE[VALUEFROM2]<=IF(B$3:F$3="";LOOKUPTABLE[VALUEFROM2];B$3:F$3))
*(LOOKUPTABLE[VALUETO2]>=IF(B$3:F$3="";LOOKUPTABLE[VALUETO2];B$3:F$3))
*(LOOKUPTABLE[VALUEFROM3]<=IF(B$4:F$4="";LOOKUPTABLE[VALUEFROM3];B$4:F$4))
*(LOOKUPTABLE[VALUETO3]>=IF(B$4:F$4="";LOOKUPTABLE[VALUETO3];B$4:F$4))
*(LOOKUPTABLE[RESULT])))}
Do you have some ideas, how I could solve this problem?
Thanks a lot in advance for any suggestions!
Netraam