Adding products of the pairs not working in excel 2019

Hello dear members,

What I'm trying to figure out is why the same formula returns #VALUE! in excel 2019, but returns a perfect result in Office 365.
If I open the attached file in Microsoft 365 it calculates the sum of the same pair products in the two tabels (A*A + B*B + C*C + D*D)
In Excel 2019 it gives me #VALUE! error.
Please advice.

Thank you !

In Excel 2019, it is an array formula and needs to be set with CTRL-SHIFT-ENTER.

Unfortunately I've already tried that, not working, same result.
Thank you for reply.

Even then, it will possibly/probably return the wrong answer as it will ONLY lookup the first value in the array not all of them.

In Microsoft 365 it works perfectly though.
Is there an alternative formula for my goal? Thank you!

If array entered does it still return an error... or 24.

I array, it returns still #VALUE!
I've modified the formula and if I leave only a value on the second array of sumproducts it returns 24:

Formula:

Please Login or Register  to view this content.

The vlookup can not lookup multiple values pre-O365. You need a totally different approach.

Please try

=SUMPRODUCT(VLOOKUP(T(INDEX(+B3:B6,)),F3:G6,2,0),C3:C6)

or

=SUMPRODUCT(SUMIFS(G3:G6,F3:F6,B3:B6),C3:C6)

or

=SUMPRODUCT(VLOOKUP(T(IF(1,+B3:B6)),F3:G6,2,0),C3:C6)

Confirm with CTRL+SHIFT+ENTER

Like a charm!
Thank you Bo_Ry ! Thank you Glenn!