Well, I'm trying to form a complete mental picture of how this works. I started with some formulae that manipulate arrays without <Ctrl-Shift-Enter>:
Code:
=INDEX({1,3,5},2)
=INDEX({1,3,5}*PI(),2)
=INDEX({1,3,5}/{1,2,3},2)
These display 3, 9.424777961 and 1.5, respectively. So far, so good. Next sequence:
Code:
=MATCH(3,{1,3,5},0)
=MATCH(5,{1,3,5}+2,0)
=MATCH(-4,{1,3,5}-{9,7,5},0)
These all display 2.
What I've established so far, you see, is that it isn't necessary to use <Ctrl-Shift-Enter> in order to do array arithmetic. But sometimes it is. When is it necessary, and when isn't it? That's what I'm trying to work out. Next I created a 20-row table with =ROW()*5 in column 1 (5, 10, 15...) and =RC1+2 in column 2 (7, 12, 17...), and tried these:
Code:
=INDEX(R1C:R20C,2)
=INDEX(R1C:R20C+2,2)
=INDEX(R1C:R20C*R1C2:R20C2,2)
These display 10, 12 and 120; all correct.
Code:
=MATCH(15,R1C:R20C,0)
=MATCH(45,R1C:R20C*3,0)
=MATCH(255,R1C:R20C*R1C2:R20C2,0)
Woops! Now something's changed. The first one displays 3, as expected, but the other two, which I thought also would display 3, instead say #VALUE!—an error code, by the way, which isn't mentioned in MATCH's documentation.
But why? When I supply "manual" arrays to INDEX and MATCH, with and without array manipulation, they work fine. And INDEX works, also, when I substitute an array range for a "manual" array, but MATCH chokes as soon as I try array arithmetic with it. What's the difference? And why does MATCH care about that, but not INDEX?
As for your question ("How does the formula bar claim it's working?"), take a look at the attached screen shot. I selected the second MATCH function, the one that used the range*3, then clicked on the
fx button next to the formula bar, and this is what came up. It shows that Excel is correctly interpreting each argument of the MATCH function, that the result of the array arithmetic is correct ({15;30;45...}) and even that the result of the MATCH function is 3. But what's actually displayed is not the result of the MATCH, but #VALUE!.
When I use <Ctrl-Shift-Enter> with those last two, they work fine.
In case you want to see the original, and perhaps convert back to A1 notation if you don't trust R1C1, I've attached the workbook; see Sheet2 for all the samples.