Excel - Generalizing a SUMPRODUCT formula as an array formula

Question

Dear Stackoverflow members,

Thanks to the fabulous help of some MrExcel members ( link ) 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).

Input data

The look-up table looks as follows: LOOKUPTABLE

Wished result

The table that I'm trying to make, looks as follows: WISHED RESULT

Current solution

As a formula for the Result, I use the following (e.g. for the top-left result cell):

B8 = 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 or VALUE2 or VALUE3 might be empty)

This gives the following result: FINAL RESULT

Is there a better solution for big tables? E.g. an array formula?

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.

What I tried (how I failed)

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 suggestions?

Do you have some ideas, how I could solve this problem?

Thanks a lot in advance for any suggestions!

ImaginaryHuman072889 ImaginaryHuman072889 · Accepted Answer · 2018-08-23T15:47:02

Using SUMPRODUCT and MAX is unnecessary in this case since you are just looking up from a table. I recommend using just an INDEX/MATCH approach without SUMPRODUCT.

See example below, I don't have it as a pivot table but I still used your data and notice that I got the same results you did.

Note this is still an array formula. Not sure if this will solve your efficiency problem though. It might, but the bottom line is that you still have a bunch of conditions to check. The only way to pick which row has a match is to compare the condition for each column to see if a match occurs.

Excel - Generalizing a SUMPRODUCT formula as an array formula

1 Answers