Generalizing a SUMPRODUCT formula as an array formula

[netraam]

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:

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