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:
<tbody>
</tbody>
The table that I'm trying to make, looks as follows:
<tbody>
</tbody>
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:
<tbody>
</tbody>
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:
CATEGORY | VALUEFROM1 | VALUETO1 | VALUEFROM2 | VALUETO2 | VALUEFROM3 | VALUETO3 | RESULT |
A | 0 | 999 | 0 | 499 | 100 | 9999 | 0.4 |
A | 1000 | 9999 | 0 | 499 | 100 | 9999 | 0.1 |
A | 0 | 999 | 500 | 9999 | 100 | 9999 | 0.4 |
A | 1000 | 9999 | 500 | 9999 | 100 | 9999 | 0.3 |
A | 0 | 9999 | 0 | 9999 | 0 | 99 | 0.1 |
B | 0 | 999 | 0 | 9999 | 0 | 9999 | 0.5 |
B | 1000 | 9999 | 0 | 9999 | 0 | 9999 | 0.9 |
C | 0 | 89 | 0 | 9999 | 0 | 9999 | 0.3 |
C | 90 | 9999 | 0 | 9999 | 0 | 9999 | 0.1 |
<tbody>
</tbody>
The table that I'm trying to make, looks as follows:
Numerical criteria (VALUE1, VALUE2 and VALUE3) | |||||
VALUE1 | 55 | 90 | 30 | 432 | 43 |
VALUE2 | 800 | 700 | 300 | 23 | 457 |
VALUE3 | 50 | 60 | 0 | 900 | 754 |
Result | |||||
CATEGORY | |||||
A | |||||
B | |||||
C |
<tbody>
</tbody>
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:
Numerical criteria (VALUE1, VALUE2 and VALUE3) | |||||
VALUE1 | 55 | 90 | 30 | 432 | 43 |
VALUE2 | 800 | 700 | 300 | 23 | 457 |
VALUE3 | 50 | 60 | 0 | 900 | 754 |
Result | |||||
CATEGORY | |||||
A | 0.1 | 0.1 | 0.1 | 0.4 | 0.4 |
B | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 |
C | 0.3 | 0.1 | 0.3 | 0.1 | 0.3 |
<tbody>
</tbody>
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