Excel Sumproduct with multiple criteria lookup in two tables

Question

For a report, I need to calculate the SUMPRODUCT() of amounts in a data table multiplied by a factor value determined using a lookup table, the factor value (and thus column) to use depends on several conditions.I've made many attempts (and research) with SUMPRODUCT() and INDEX/MATCH() condition (tried also INDIRECT/ROWS uses) but I just can't get it to work properly.I'd appreciate some help to figure out what I'm doing wrong or any workable alternative (helper columns rows not being an alternative as my actual report and the lookup table are larger than the sample used to illustrate my question.Many thanks for looking at this question.

For the report in cells A1:G5 (sample), I need to calculate the SUMPRODUCT() of amount in data table (A28:M38) multiplied by a factor (F29:M38) value determined using the lookup table (A12:E24), only a sample) and based on the following conditions;

	A	B	C	D	E	F	G
1	Template	Report A		D	E	F	G
2	B			?
3	E
4	F

• Scope is equal to value in cell B1 (Report A)

• For Asset "B" (row 2) , make the sumproduct for assets "B" and "GB", for row 3, Asset "E", for row 4 asset "F"

• the factor value to use in the Data table is determined by looking up the Factor column in the lookup table (LookupTable) as per the following criteria ;

  • the factor depends on the column : match between column in row 1 and column "column" in the lookup table. E.g. for the formula in cell D2, the column is "D", thus the factor are those in rows 13 to 18 in the lookup table

  • the factor depends on the asset and entity type with the specfic additional criteria;

    • if asset is GB or F, use the corresponding factor (given the column letter matches)

    • if entity_type is populated with B or I and the asset is B or E, then use the corresponding factor (for entity_type B or I)

    • else use the factor corresponding to the asset (B or E)

Thus for D2 the result expected would = 1000*.21+2000*.2+1200*.2+3000*.33

for D3, the result should be =1000*0 + 1100*.02+1000*0.1

or D4, the result should be =1000*.8 + 500*0

The lookup table

asset	entity_type	active	column	lookup_column
GB		N	D	Factor1
	B	Y	D	Factor2
	I	Y	D	Factor3
B		Y	D	Factor1
E		Y	D	Factor1
F			D	Factor8
GB		Y	E	Factor4
	B	Y	E	Factor5
	I	Y	E	Factor6
B		Y	E	Factor7
E		Y	E	Factor7
F		Y	E	Factor8

The data table;

Scope	Name	Amount	entity_type	asset	Factor1	Factor2	Factor3	Factor4	Factor5	Factor6	Factor7	Factor8
Report A	CPY 1	1000	I	E	.2	.25
Report B	CPY 2	1500		B		.1
Report A	CPY 1	1000		B	.21	.15
Report A	CPY 3	2000		B	.2	.25
Report A	CPY 4	1200	B	GB	.2	.25	.3	.4	.5	.6	.7	.8
Report A	CPY 5	1100		E	.02	.4
Report A	CPY 1	1000		F	.22	.05	.3	.4	.5	.6	.7	.8
Report A	CPY 6	500	I	F	.12	.45
Report A	CPY7	3000		GB	.33	.2	.3	.4	.5	.6	.7	.8
Report A	CPY 8	1000	B	E	.25	.1	.3	.4	.5	.6	.7	.8

The formula for D2;

=SUMPRODUCT(
    --(Data[Scope] = $B$1);
    --(IF(
        $A2 = "B";
        (Data[asset] = "B") + (Data[asset] = "GB");
        Data[asset] = $A2
    ));
    INDEX(
        Data;
        0;
        MATCH(
            INDEX(
                LookupTable[lookup_column];
                MATCH(
                    1;
                    (LookupTable[asset] = IF($A2 = "B"; "B";$A2)) *
                    (LookupTable[entity_type] = IF(OR(Data[entity_type] = "B";Data[entity_type] = "I");Data[entity_type];"")) *
                    (LookupTable[column] = D1);
                    0
                )
            );
            Data[#Headers];
            0
        )
    ) ; Data[Amount]
) 

For what I can see from the "evaluate formula" the problem lies in the 2nd index(match()) as I can see it it has an array of 12 items with last 2 being #N/A (there are 10 rows in the data table) thus array size are not equal but they should not be, should they ?

If adding IFNA(;0) in the 2nd index/match(), the end result is 0 and it would pick up only Factor1 (which is not what I want).

That's basically where I'm stuck.

Second formula (picks always a single factor value => the 1st one from INDEX())

=SUMPRODUCT(
    --(Data[Scope] = $B$1);
    --(IF(
        OR($A2 = "B"; $A2 = "E");
        (Data[asset] = $A2) + (Data[asset] = IF($A2 = "B"; "GB"; ""));
        Data[asset] = $A2
    ));
    Data[Amount]*
    IFERROR(
        --(
            INDEX(
                Data;
                0;
                MATCH(
                    IF(
                        OR($A2="B"; $A2="E");
                        IF(
                            OR(Data[entity_type] ="B"; Data[entity_type] ="I");
                            INDEX(
                                LookupTable[lookup_column];
                                MATCH(
                                    1;
                                    (LookupTable[entity_type] = Data[entity_type]) *
                                    (LookupTable[column] = D$1);
                                    0
                                )
                            );
                            INDEX(
                                LookupTable[lookup_column];
                                MATCH(
                                    1;
                                    (LookupTable[asset] = $A2) *
                                    (LookupTable[column] = D$1);
                                    0
                                )
                            )
                        );
                        INDEX(
                            LookupTable[lookup_column];
                            MATCH(
                                1;
                                (LookupTable[asset] = $A2) *
                                (LookupTable[column] = D$1);
                                0
                            )
                        )
                    );
                    Data[#Headers];
                    0
                )
            )
        );
        0
    )
)

Excel screenshot

Answer 1

This formula using Excel365 functions allowed me to achieve the desired result;

=SUM(
BYROW(
    Data;
    LAMBDA(row;
        LET(
       et; INDEX(row; MATCH("entity_type"; Data[#Headers]; 0));
       as; INDEX(row; MATCH("asset"; Data[#Headers]; 0));
       amt; INDEX(row; MATCH("Amount"; Data[#Headers]; 0));
       scope; INDEX(row; MATCH("Scope"; Data[#Headers]; 0));
       template; $A$3;
       asset_filter; OR(
           (template = "B") * ((as = "B") + (as = "GB"));
           (template = "E") * (as = "E");(template = "F") * (as = "F")
            );
            factor_col; IF(
             OR(as = "B"; as = "E");
              IF(
                 OR(et = "B"; et = "I");
                 INDEX(
                  LookupTable[lookup_column];
                  MATCH(1; (LookupTable[entity_type] = et) * (LookupTable[column] = D$1); 0)
                    );
                    INDEX(
                        LookupTable[lookup_column];
                        MATCH(1; (LookupTable[asset] = as) * (LookupTable[column] = D$1); 0)
                    )
                );
                INDEX(
                    LookupTable[lookup_column];
                    MATCH(1; (LookupTable[asset] = as) * (LookupTable[column] = D$1); 0)
                )
            );
            factor_value; INDEX(row; MATCH(factor_col; Data[#Headers]; 0));
            IF(scope = $B$1; IF(asset_filter; amt * factor_value; 0); 0)
        )
    )
)
)

The BYROW(Data, LAMBDA(...)) processes each row of the Data table individually and the LAMBDA function (which I discovered) allows to define a series of operations for each row. The LET Function enables to define and reuse variables, which makes the final calculation easier to formulate.

The factor_col variable identifies which factor column (e.g., Factor1, Factor2) to use based on the entity_type and asset. The trick is that I want to use the entity_type (B or I ) when populated for asset B or E (and not for F or GB) => this was the difficult part to handle with the formula, together with the fact that index() in the sumproduct was not passing all values from the array but only one. For that reason I looked a way to process data on a row by row basis. Hope others find this useful

Answer 2

The following formula is used to populate the Value1 and Value2 columns: =IFERROR(INDEX(Data[[Factor1]:[Factor8]],AGGREGATE(15,6,(ROW(Data[Scope])-ROW(Data[[#Headers],[Scope]]))/(Data[[Scope]:[Scope]]=$B$2)/(Data[[asset]:[asset]]=LookupTable[@[asset]:[asset]]),COLUMNS($F13:F13)),AGGREGATE(15,6,(COLUMN(Data[[#Headers],[Factor1]:[Factor8]])-COLUMN(Data[[asset]:[asset]]))/(Data[[#Headers],[Factor1]:[Factor8]]=LookupTable[@[lookup_column]:[lookup_column]]),1))*INDEX(Data[[Amount]:[Amount]],AGGREGATE(15,6,(ROW(Data[Scope])-ROW(Data[[#Headers],[Scope]]))/(Data[[Scope]:[Scope]]=$B$2)/(Data[[asset]:[asset]]=LookupTable[@[asset]:[asset]]),COLUMNS($F13:F13))),0)

The following is used to populate cells beneath D and E: =SUM(SUMPRODUCT((LookupTable[[Value1]:[Value2]])*(LookupTable[[asset]:[asset]]=$A3)*(LookupTable[[column]:[column]]=D$2)),IF($A3="B",SUMPRODUCT((LookupTable[[Value1]:[Value2]])*(LookupTable[[asset]:[asset]]="GB")*(LookupTable[[column]:[column]]=D$2)),0))

Note that I don't understand how D3 has three factors when there are only two instances of asset E.