Forum Discussion
Array formula
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.
5 Replies
Danielv310 ,,
Let me explain the real mechanism, that how the formula works:
- Cell E3 has formula:
=VLOOKUP($D$3:$D$5,Fruit,2,FALSE)- Extract expected values, But in cell E6 returns #VALUE error,, cozz E6 is out of the INPUT range D3:D5.
- To avoid this I've used IFERROR in cell F3, also used cell by cell method:
=IFERROR(VLOOKUP(D3,Fruit,2,FALSE),"")Since D3:D5 is identical to A3:A4, Excel reads correct value and returns quantities.
- Now check VLOOKUP 2,, order has been changed & formula in E9 returns quantity along with @#NA and #VALUE error, which has been handled by IFERROR with formula in F9.
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.
- Check VLOOKUP 3, cell E16 has array formula,
{=VLOOKUP($D$16:$D$18,Fruit,2,FALSE)}
and if you drag it down returns same quantity 10.
- Reason is in every cell formula only read first cell in range $D$16:$D$18.
- If you alter the formula and use RELATIVE Reference and drag, you get correct quantities, check formula in cell F16.
{=VLOOKUP(D16:D18,Fruit,2,FALSE)}
Solution:
If you need array formula in all cell,,
- Write formula in cell G16, finish with Ctrl+Shift+Enter , fill down.
- Select G16:G18, press F2 and finish with Ctrl+Shift+Enter.
Adjust cell references in the formula as needed.
Check the attached file for formula details.
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
