Morning, I ended up using a helper column in the lookup data to create distinct keys and using VLOOKUP to retrieve required data.

Array formula that now works ($A$1 contains last used row number of sheet, within INDIRECT):

Code:

{=IFERROR((
SUMPRODUCT(SUMIFS(Data!$R$5:$R$146,Data!$O$5:$O$146,INDIRECT("$D$14:$D$"&$A$1),Data!$P$5:$P$146,INDIRECT("$E$14:$E$"&$A$1),Data!$Q$5:$Q$146,INDIRECT("$F$14:$F$"&$A$1)),L14:L16)-
SUMPRODUCT(SUMIFS(Data!$S$5:$S$146,Data!$O$5:$O$146,INDIRECT("$D$14:$D$"&$A$1),Data!$P$5:$P$146,INDIRECT("$E$14:$E$"&$A$1),Data!$Q$5:$Q$146,INDIRECT("$F$14:$F$"&$A$1)),L14:L16))/
SUMPRODUCT(SUMIFS(Data!$R$5:$R$146,Data!$O$5:$O$146,INDIRECT("$D$14:$D$"&$A$1),Data!$P$5:$P$146,INDIRECT("$E$14:$E$"&$A$1),Data!$Q$5:$Q$146,INDIRECT("$F$14:$F$"&$A$1)),L14:L16), "")}

*Using spacing to show it's still (A-B)/A*

I also discovered applying OFFSET to a named ranges (2D table) to isolate a single column returns #VALUE if used within an array formula, so this would error:

Code:

{=IFERROR((SUMPRODUCT(SUMIFS(OFFSET(Table_Rates,,5,1),OFFSET(Table_Rates,,2,1),INDIRECT("$D$14:$D$"&$A$1),OFFSET(Table_Rates,,3,1),INDIRECT("$E$14:$E$"&$A$1),OFFSET(Table_Rates,,4,1),INDIRECT("$F$14:$F$"&$A$1)),L14:L16)... etc}

## Like this thread? Share it with others