Array formulae with different sized array, e.g. array-if() or somproduct-if()?

Dear All,

I am confused and honestly not so sure, whether Excel could achieve what I want, i.e. a sort of "array-if-formula":
In the end I want to express a “sumproduct” of two arrays that do not have the same size in the first place. One is bigger than the other but once my criteria is matched they consist of exactly the same number of elements that need to be multiplied with with each other and summed up in the end.
Please find in the illustrative example those arrays and different ways to achieve what I want. The first approach achieves my goal literally step by step and the two others are a little bit more straight to the point. You see, I can meet my goal with the help of intermediate step(s) but in the end I would like to get there within ONE step, even though that step might be a little more complicated.
The current attempts to do this are in the red Frame: Unfortunaelty neither the use of index() nor vlookup() is sufficient within an array-formula as such, because it seems that these two formulae do not support the array-idea in general. Both of them do multiply the elements of the other smaller array with the first figure of the bigger one, only (pls compare: “X-Check of the approach in question”.

I would appreciate your support, even if the final outcome might be that excel cannot do this...!

Thanks a lot in advance and kind regards from Hamburg


HLHans