Oct 03 2020 05:19 PM
I understand the array formulas since 2018 on have changed so in my example below, I can type the array formula =VLOOKUP(A9:A11,Fruit,2,FALSE) into cell B9 hit enter and Excel will perform the same task as if I selected cells B15:B17 and typed in B15 =VLOOKUP(A15:A17,Fruit,2,FALSE) and then pressed CTRL+Shift+Enter.
I actually am not concerned about this new way of entering array formulas. What I need to know is if someone gives me an Excel sheet where I see the formulas as in B15:B17 with the braces, {=VLOOKUP(A15:A17,Fruit,2,FALSE)} is there any way to enter that array formula where that is what I see other than typing it in and then holding down the CTRL+Shift+Enter?
I know this is an odd question, so thanks in advance.
Oct 03 2020 11:19 PM
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.
Oct 04 2020 02:50 AM - edited Oct 04 2020 03:04 AM
@Danielv310 ,,
Let me explain the real mechanism, that how the formula works:
=VLOOKUP($D$3:$D$5,Fruit,2,FALSE)
=IFERROR(VLOOKUP(D3,Fruit,2,FALSE),"")
Since D3:D5 is identical to A3:A4, Excel reads correct value and returns quantities.
Therefore using cell by cell method, instead of a Range as INPUT is always better and wise.
Now let me analyse the core issue, that how do get array formula in All Cells, in your sheet B15:B17 and in my attached sheet G16:G18.
{=VLOOKUP($D$16:$D$18,Fruit,2,FALSE)}
and if you drag it down returns same quantity 10.
{=VLOOKUP(D16:D18,Fruit,2,FALSE)}
Solution:
If you need array formula in all cell,,
Adjust cell references in the formula as needed.
Check the attached file for formula details.
Oct 04 2020 09:44 AM - edited Oct 04 2020 09:46 AM
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?
Oct 04 2020 10:33 AM
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.
Oct 04 2020 01:30 PM
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