Hello,
I've created this array formula that puzzles me.
If I enter this over 2 or more cells, it gives me the correct result in both cells. However, if I put it in a single cell it comes up with a completely different number.
Basically, what it is supposed to do is:
from a list of countries look up the Base Currency then use this base currency to look up the exchange rate for that currency and multiply the amount behind the country with that and add the results for the different countries.
Can someone explain the logic behind the difference between single cell and multiple cell array formulae?
Cheers,
Roel
I've created this array formula that puzzles me.
Code:
{=SUMPRODUCT(INDEX(Support!R1C4:R6C5,MATCH(INDEX(Support!R1C1:R14C2,MATCH(LEFT(VSource!R2C1:R17219C1,2),Support!R1C1:R14C1,FALSE),2),Support!R1C4:R6C4,FALSE),2)*VSource!R2C13:R17219C13)}If I enter this over 2 or more cells, it gives me the correct result in both cells. However, if I put it in a single cell it comes up with a completely different number.
Basically, what it is supposed to do is:
from a list of countries look up the Base Currency then use this base currency to look up the exchange rate for that currency and multiply the amount behind the country with that and add the results for the different countries.
Can someone explain the logic behind the difference between single cell and multiple cell array formulae?
Cheers,
Roel