Forum Discussion
Array formula
The issue should not occur too often because a lot of work was put in to recognising dynamic array versions of Excel and automatically ,converting formulae as the workbook is moved between versions.
Nevertheless, to convert a CSE array to DA, select the entire array and re-commit the formula with Ctrl+Enter. All but the final term will generate #SPILL! errors and any relative references will be wrong, except for the first cell. Delete all the formula copies except the first.
Thanks for your reply. I didn't give enough of the a context for why I was asking my question, but I think you answered based on what a reasonable presumption of why I might have asked.
Let me give a little more context. I teach a class that introduces students to Excel. Array formulas are way beyond the class. They were supposed to use a VLookup function in its simplest form, and there is no need to use an array formula. The answer should have been what is in VLookup3 below. I received several answers that used the CSE array formula. That seemed very strange to me. If I had gotten the DA, it is highly unlikely but perhaps conceivable someone could accidently back into that version, but with the CSE and its need to hit CTRL+Shift+Enter, I don't think trial and error would back you into a CSE array formula. That's why I wanted to know if there are other ways to call the CSE array formula other than using CTRL+Shift+Enter. Is there any other way one could accidently back into it?
I have figured out the answer to my own question. Due to a licensing agreement, I have Office build 1808 and not the most current version of Office 365 on my work computer. Evidently Dynamic Arrays are not a part that build of Office 2019. As a result DAs are converted back to CSE arrays.
Thank you everyone for trying to help.
dan
I don't know other ways, but shifting back and forth between CSE and DA Excels is not very unrealistic situation. Due to Excel Online. Having desktop with CSE Excel, in couple of minutes you may create Microsoft account which by default have Office Online within which is by default DA Excel.
