Array formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1742789%22%20slang%3D%22en-US%22%3EArray%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1742789%22%20slang%3D%22en-US%22%3E%3CP%3EI%20understand%20the%20array%20formulas%20since%202018%20on%20have%20changed%20so%20in%20my%20example%20below%2C%20I%20can%20type%20the%20array%20formula%20%3DVLOOKUP(A9%3AA11%2CFruit%2C2%2CFALSE)%20into%20cell%20B9%20hit%20enter%26nbsp%3Band%20Excel%20will%20perform%20the%20same%20task%20as%20if%20I%20selected%20cells%20B15%3AB17%20and%20typed%20in%20B15%20%3DVLOOKUP(A15%3AA17%2CFruit%2C2%2CFALSE)%20and%20then%20pressed%20CTRL%2BShift%2BEnter.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20actually%20am%20not%20concerned%20about%20this%20new%20way%20of%20entering%20array%20formulas.%26nbsp%3B%20What%20I%20need%20to%20know%20is%20if%20someone%20gives%20me%20an%20Excel%20sheet%20where%20I%20see%20the%20formulas%20as%20in%20B15%3AB17%20with%20the%20braces%2C%20%7B%3DVLOOKUP(A15%3AA17%2CFruit%2C2%2CFALSE)%7D%20is%20there%20any%20way%20to%20enter%20that%20array%20formula%20where%20that%20is%20what%20I%20see%20other%20than%20typing%20it%20in%20and%20then%20holding%20down%20the%26nbsp%3BCTRL%2BShift%2BEnter%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20this%20is%20an%20odd%20question%2C%20so%20thanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22array.JPG%22%20style%3D%22width%3A%20567px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223958iC8977EBA15E1256A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22array.JPG%22%20alt%3D%22array.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1742789%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1742971%22%20slang%3D%22en-US%22%3ERe%3A%20Array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1742971%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F819054%22%20target%3D%22_blank%22%3E%40Danielv310%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20should%20not%20occur%20too%20often%20because%20a%20lot%20of%20work%20was%20put%20in%20to%20recognising%20dynamic%20array%20versions%20of%20Excel%20and%20automatically%20%2Cconverting%20formulae%20as%20the%20workbook%20is%20moved%20between%20versions.%3C%2FP%3E%3CP%3ENevertheless%2C%20to%20convert%20a%20CSE%20array%20to%20DA%2C%20select%20the%20entire%20array%20and%20re-commit%20the%20formula%20with%20Ctrl%2BEnter.%26nbsp%3B%20All%20but%20the%20final%20term%20will%20generate%20%23SPILL!%20errors%20and%20any%20relative%20references%20will%20be%20wrong%2C%20except%20for%20the%20first%20cell.%26nbsp%3B%20Delete%20all%20the%20formula%20copies%20except%20the%20first.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1743050%22%20slang%3D%22en-US%22%3ERe%3A%20Array%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1743050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F819054%22%20target%3D%22_blank%22%3E%40Danielv310%3C%2FA%3E%26nbsp%3B%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ELet%20me%20explain%20the%20real%20mechanism%2C%20that%20how%20the%20formula%20works%3A%3C%2FEM%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1601805059808.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223983iE060587225C689B8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rajesh-S_0-1601805059808.png%22%20alt%3D%22Rajesh-S_0-1601805059808.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ECell%20E3%20has%20formula%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(%24D%243%3A%24D%245%2CFruit%2C2%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EExtract%20expected%20values%2C%26nbsp%3BBut%20in%20cell%20E6%20returns%20%23VALUE%20error%2C%2C%20cozz%20E6%20is%20out%20of%20the%20INPUT%20range%20D3%3AD5.%3C%2FLI%3E%3CLI%3ETo%20avoid%20this%20I've%20used%20IFERROR%20in%20cell%20F3%2C%20also%20used%20cell%20%3CSPAN%3Eb%3C%2FSPAN%3Ey%20cell%20method%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFERROR(VLOOKUP(D3%2CFruit%2C2%2CFALSE)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ESince%20D3%3AD5%20is%20identical%20to%20A3%3AA4%2C%20Excel%20reads%20correct%20value%20and%20returns%20quantities.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ENow%20check%26nbsp%3B%20VLOOKUP%202%2C%2C%20order%20has%26nbsp%3B%3CSPAN%3Ebeen%20changed%20%26amp%3B%20formula%20in%20E9%20returns%20quantity%20along%20with%26nbsp%3B%40%23NA%20and%20%23VALUE%20error%2C%20which%20has%26nbsp%3Bbeen%20handled%26nbsp%3Bby%20IFERROR%20with%20formula%20in%20F9.%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CEM%3E%3CSPAN%3ETherefore%20using%26nbsp%3B%3C%2FSPAN%3Ecell%26nbsp%3B%3CSPAN%3Eb%3C%2FSPAN%3Ey%20cell%20method%2C%20instead%20of%20a%20Range%20as%20INPUT%20is%20always%26nbsp%3B%3CSPAN%3Ebetter%20and%20wise.%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3ENow%20let%20me%20analyse%20the%20core%20issue%2C%20that%20how%20do%20get%20array%20formula%20in%20All%20Cells%2C%20in%20your%20sheet%26nbsp%3BB15%3AB17%20and%20in%20my%20attached%20sheet%20G16%3AG18.%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSPAN%3ECheck%20VLOOKUP%203%2C%20cell%20E16%20has%20array%20formula%2C%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%3E%7B%3DVLOOKUP(%24D%2416%3A%24D%2418%2CFruit%2C2%2CFALSE)%7D%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eand%20if%20you%20drag%20it%20down%20returns%20same%20quantity%2010.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSPAN%3EReason%20is%20in%20every%20cell%20formula%20only%20read%20first%20cell%20in%20range%20%24D%2416%3A%24D%2418.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIf%20you%20alter%20the%20formula%20and%20use%20RELATIVE%20Reference%20and%20drag%2C%20you%20get%20correct%20quantities%2C%20check%20formula%20in%20cell%20F16.%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%3E%7B%3DVLOOKUP(D16%3AD18%2CFruit%2C2%2CFALSE)%7D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3ESolution%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20you%20need%20array%20formula%20in%20all%20cell%2C%2C%3C%2FSPAN%3E%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSPAN%3EWrite%20formula%20in%20cell%20G16%2C%20finish%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%20%2C%26nbsp%3B%3C%2FSTRONG%3Efill%20down.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3ESelect%20G16%3AG18%2C%20press%20F2%20and%20finish%20with%26nbsp%3B%3CSTRONG%3ECtrl%2BShift%2BEnter.%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CEM%3E%3CSTRONG%3EAdjust%20cell%20references%26nbsp%3Bin%20the%20formula%20as%20needed.%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3ECheck%20the%20attached%20file%20for%20formula%20details.%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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.

 

array.JPG

5 Replies
Highlighted

@Danielv310 

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.

Highlighted

@Danielv310 ,,

 

Let me explain the real mechanism, that how the formula works:

 

Rajesh-S_0-1601805059808.png

 

  • 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.

Highlighted

@Peter Bartholomew 

 

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?

 

Danielv310_0-1601829958242.png

 

Highlighted

@Danielv310 

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.

Highlighted

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