Resolved: I STILL DON'T KNOW HOW THIS WORKS
My issue is a "solution" problem. I did the exercise and I got the desired output but can't explain how and why it works.
If anyone would be so generous to explain to me how ranges D20:F24 know they are to pick from the Feb, Mar and Apr columns respectively.
Hey Jonathan,
Good to hear from you! This is a very interesting case.
So, basically the CHOOSE function is navigated by the value in row B20 -> because it says "1", you'll pick a value from the range C6:N6. Now, here's why you see the reference to D in the interval $C$6:$N$6.
Excel evaluates the array relative to the position of the formula. If the formula is in cell D20, and the chosen array is $C$6:$N$6, Excel returns the value from the cell that aligns with D20 within the $C$6:$N$6 range. This is because arrays in functions like CHOOSE are returned as a whole block, and Excel aligns the output based on where the formula is executed. So in D20, it looks at the relative position within the chosen array, which would correspond to cell D6.
Hope this helps!
Best,
Ned
Oh! Thank you for the explanation and insight. So what this means if I understood clearly is that for my CHOOSE() function to work effectivel and as I intend the column letter of the destination cell and the column letter of the reference cell ought to be the same?
Is my statement correct?
Hi Jonathan and Ned!
I hope you don't mind if I join the conversation.
@Jonathan: They are the same. You can paste the table from this exercise from C20:F24 into, say, G20:J24 to observe how the output will automatically adapt.
Hope this helps.
Best,
Martin,
The 365 Team
Thank you Ganchev and Ned!
Both your explanations were helpful.