Home
%3CLINGO-SUB%20id%3D%22lingo-sub-254199%22%20slang%3D%22en-US%22%3EExcel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-254199%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%26nbsp%3Bour%26nbsp%3Bsecond%26nbsp%3Bwave%20of%20Excel%20Office%20365%20performance%20improvements%26nbsp%3Bafter%26nbsp%3Bour%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FExcel-O365-performance-improvements-in-September%2Fba-p%2F105714%22%20target%3D%22_blank%22%3Efirst%26nbsp%3BSept%20%E2%80%9917%26nbsp%3Bone%2C%3C%2FA%3E%20to%26nbsp%3Breinforce%20our%20commitment%26nbsp%3Bto%26nbsp%3Bfixing%20top%20impacting%26nbsp%3Bfreezing%2C%20slow%20and%20not%20responding%20performance%20issues%26nbsp%3Bderived%26nbsp%3Bfrom%20Excel%20user%20feedback%20and%20usage%26nbsp%3Blearnings.%26nbsp%3BThis%20time%20around%20we%20have%20even%20more%26nbsp%3Bimprovements%26nbsp%3Bthan%20before%26nbsp%3Bstarting%20with...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20style%3D%22width%3A%20200%3B%20float%3A%20left%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F50121iCF7F41C929846016%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20alt%3D%22Lookup%20functions.png%22%20title%3D%22Lookup%20functions.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%3CSTRONG%3ELookup%20functions%20%3C%2FSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fvlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EVLOOKUP%3C%2FA%3E%2C%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fhlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EHLOOKUP%3C%2FA%3E%2C%20and%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fmatch-function-e8dffd45-c762-47d6-bf89-533f4a37673a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMATCH%3C%2FA%3E%20are%20one%20of%20the%20most%20used%20functions%20in%20Excel.%20If%20you%20use%20them%20with%20exact%20match%20to%20find%20items%20in%20a%20table%20or%20range%20in%20Excel%2C%20and%20see%20it%20noticeably%20take%20time%20in%20seconds%20or%20minutes%20or%20more%2C%20chances%20are%20you'll%20now%20see%20very%20noticeable%20improvement%20in%20the%20speed%20at%20which%20you%20see%20results.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CIMG%20style%3D%22float%3A%20left%3B%20padding%3A%200%2020px%2020px%200%3B%22%20src%3D%22https%3A%2F%2F8gportalvhdsf9v440s15hrt.blob.core.windows.net%2Flithium%2FExcel%2FSpeedy-LOOKUP-final_small.gif%22%20border%3D%220%22%20width%3D%22525%22%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EWe%20made%20them%20faster%3C%2FSTRONG%3E%20by%20more%20efficiently%20finding%20the%20item%20you%20are%20looking%20up.%20We%20create%20an%20index%20on-demand%20when%20you%20first%20search%20of%20a%20range%20of%20cells%20and%20then%20reuse%20it%20in%20subsequent%20lookups%20from%20the%20same%20range%2C%20until%20data%20changes%20in%20the%20lookup%20range.%26nbsp%3BFor%20details%20refer%20to%20our%20updated%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Fvba%2Fexcel-vba%2Farticles%2Fexcel-performance-and-limit-improvements%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EExcel%20Performance%20and%20limit%20improvements%20whitepaper%3C%2FA%3E%3C%2FSPAN%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%2C%20we%20have%20improved%20the%20%3CSTRONG%3Espeed%20%3C%2FSTRONG%3Eof%20%3CSTRONG%3Emany%20use%20cases%20involving%20operations%20like%20copy%2Fpaste%2C%20undo%2C%20conditional%20formatting%2C%20cell%20editing%2C%20cell%20selection%2C%20scrolling%2C%20filtering%2C%20file%20open%2C%20%3C%2FSTRONG%3Eand%3CSTRONG%3E%20programmability%3C%2FSTRONG%3E%2C%20such%20as%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3ECopying%20the%20entire%20column%3C%2FSTRONG%3E%20in%20a%20sheet%20and%20selecting%20the%20clipboard%20is%20faster%20now%20because%20we%20optimized%20our%20large%20memory%20allocations%20with%20a%20more%20efficient%20data%20structure.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EUndoing%20pasted%20cells%20with%20conditional%20formatting%3C%2FSTRONG%3E%20was%20slow%20because%20a%20paste%20operation%20was%20inefficient%20in%20generating%20an%20undo%20record%20for%20every%20cell%20with%20conditional%20formatting%20and%20with%20its%20own%20changed%20priority.%20It%20now%20combines%20them%20more%20efficiently%20into%20one%20undo%20record%20with%20changed%20priorities%20for%20all%20in%20it.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EUndoing%20inserting%20a%20new%20row%3C%2FSTRONG%3E%20with%20a%20copied%20range%20with%20merged%20cells%20is%20now%20faster%20because%20we%20optimized%20handing%20of%20merged%20cells%20during%20an%20undo%20operation.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EDeleting%20one%20or%20more%20rows%20with%20merged%20cells%3C%2FSTRONG%3E%20is%20now%20more%20performant%20because%20we%20skip%20redundant%20calls%20to%20render%20the%20visible%20grid%20in%20the%20midst%20of%20the%20deletion%20operation.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EViewing%20filter%20drop%20down%3C%2FSTRONG%3E%20for%20a%20column%20with%20lots%20of%20cells%20with%20conditional%20formatting%20is%20now%20faster%20because%20we%20optimized%20the%20filter%20drop%20down%20algorithm%20to%20be%20more%20efficient%20computing%20highlight%2C%26nbsp%3Bunique%2C%20and%20duplicate%20values.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EScrolling%20in%20the%20visible%20sheet%3C%2FSTRONG%3E%2C%20after%20an%20operation%20wasn%E2%80%99t%20optimal%2C%20because%20we%20re-rendered%20(%3CEM%3Eor%20at%20least%20evaluated%3C%2FEM%3E)%20all%20rows%20top%20to%20bottom%20(%3CEM%3Eincluding%20filtered%20rows%3C%2FEM%3E)%20in%20the%20visible%20sheet.%20This%20can%20be%20expensive%20depending%20on%20the%20number%20of%20rows%20and%20our%20ability%20to%20calculate%20for%20animation%20related%20rendering.%20Our%20optimization%20introduced%20in%20our%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FExcel-O365-performance-improvements-in-September%2Fba-p%2F105714%22%20target%3D%22_blank%22%3Efirst%20wave%20of%20performance%20improvements%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EScrolling%2C%20editing%20of%20cells%2C%20cell%20selection%2C%20filtering%20operations%3C%2FSTRONG%3E%20in%20the%20grid%20are%20much%20faster%20when%20lots%20of%20rows%20are%20filtered%20%2F%20hidden%20because%20our%20rendering%20is%20more%20optimized%20to%20intelligently%20defer%20expensive%20rendering%20calls%20until%20we%20have%20calculated%20last%20row%20in%20the%20visible%20range.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3ESlow%20transitioning%20to%20the%20next%20cell%20after%20editing%3C%2FSTRONG%3E%20a%20cell%20adjacent%20to%20a%20large%20table%20or%20range%20of%20cells%20with%20data%2C%20because%20after%20editing%20a%20cell%20adjacent%20to%20a%20large%20table%20or%20range%20of%20cells%20with%20data%2C%20we%20would%20generate%20a%20preview%20of%20all%20cells%20in%20that%20column%20to%20Flash%20Fill%2C%20which%20is%20on%20by%20default.%20Depending%20on%20the%20size%20of%20the%20adjacent%20table%20or%20range%20of%20cells%20and%20whether%20the%20cells%20contain%20additional%20metadata%20like%20conditional%20formatting%2C%20this%20can%20be%20a%20time%20consuming%20and%20expensive%20operation.%20We%20now%2C%20limit%20scope%20of%20Flash%20Fill%20preview%20to%20the%20visible%20area%20improving%20Excel%20responsiveness.%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3EWhen%20%3CSTRONG%3Eopening%20any%20workbook%3C%2FSTRONG%3E%20we%20searched%20all%20existing%20ribbon%20content%20to%20ensure%20every%20component%20of%20the%20ribbon%20renders%20correctly.%20We%20have%20now%20made%20open%20faster%20by%20more%20intelligently%20searching%20in%20common%20cases%20like%20when%20the%20ribbon%20item%20being%20searched%20for%20doesn%E2%80%99t%20exist.%20Also%2C%20during%20open%20we%20now%20skip%20updating%20MRU%20links%20synchronously%20and%20instead%20update%20them%20asynchronously%20after%20open.%20Finally%2C%20we%20scope%20the%20rendering%20of%20the%20grid%20to%20the%20visible%20grid%20area%20when%20opening%20workbooks%20with%20lots%20of%20wrapped%20text%20in%20locales%20like%20Japanese.%3C%2FLI%3E%0A%3CLI%3EOpening%20a%20local%20workbook%20when%20using%20a%203%3CSUP%3Erd%3C%2FSUP%3E%20party%20anti-virus%20software%20is%20faster%20because%20we%20are%20more%20intelligent%20in%20avoiding%20searching%20for%20the%203%3CSUP%3Erd%3C%2FSUP%3E%20party%20anti-virus%20vendor%20registered%20for%20scanning%20when%20a%20scan%20is%20determined%20to%20be%20redundant.%3C%2FLI%3E%0A%3CLI%3EWe%20%3CSTRONG%3Eopen%20simple%20CSV%20text%20files%3C%2FSTRONG%3E%20much%20faster%20by%20employing%20a%20more%20efficient%20memory%20allocation.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EFlashing%20scrollbars%20and%20slower%20running%20VBA%20code%3C%2FSTRONG%3E%20(%3CEM%3Ethan%20Excel%202010%3C%2FEM%3E)%20even%20when%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2FExcel.Application.ScreenUpdating%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EScreenUpdating%20property%3C%2FA%3E%3C%2FSPAN%3E%20is%20set%20to%20false.%20We%20have%20addressed%20this%20by%20ensuring%20scrollbars%20will%20no%20longer%20update%20when%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2FExcel.Application.ScreenUpdating%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EScreenUpdating%20property%3C%2FA%3E%3C%2FSPAN%3E%20is%20set%20to%20false%20(%3CEM%3Ejust%20like%20in%20Excel%202010%3C%2FEM%3E).%20This%20in%20turn%20diverts%20CPU%20cycles%20away%20from%20redundant%20rendering%20of%20the%20scrollbars%20and%20to%20running%20user%20VBA%20code%2C%20improving%20code%20execution%20speed.%3C%2FLI%3E%0A%3CLI%3EProgrammatically%20%3CSTRONG%3Eselecting%20a%20cell%2C%20a%20range%20of%20cells%2C%20or%20a%20sheet%20using%20VBA%3C%2FSTRONG%3E%20is%20faster%20because%20we%20avoid%20redundant%20calls%20for%20updating%20a%20document%E2%80%99s%20upload%20status%20when%20running%20VBA%20macro%20code%20performing%20operations%20like%20selecting%20a%20cell%2C%20a%20range%20of%20cells%2C%20or%20a%20sheet.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EExecuting%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Ffrequency-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9%3FNS%3DEXCEL%26amp%3BVersion%3D16%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL160%26amp%3BHelpId%3Dxlmain11.chm60300%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFrequency%20function%3C%2FA%3E%3C%2FSPAN%3E%20for%20large%20data%20sets%3C%2FSTRONG%3E%20is%20faster%20because%20we%20optimized%20the%20underlying%20sorting%20algorithm%20for%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Ffrequency-function-44e3be2b-eca0-42cd-a3f7-fd9ea898fdb9%3FNS%3DEXCEL%26amp%3BVersion%3D16%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL160%26amp%3BHelpId%3Dxlmain11.chm60300%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFrequency%20function%3C%2FA%3E%3C%2FSPAN%3E%20for%20large%20data%20sets.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EOffice%20365%20can%20help%20you%20be%20even%20more%20productive.%3CSUP%3E1%3C%2FSUP%3E%20Give%20it%20a%20try%20today!%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3ENow%20we%20know%20we%20are%20not%20done%20with%20this%20second%20wave%20and%20there%20is%20more%2C%20that%20said%20we%20would%20really%20like%20to%20hear%20from%20you%20to%20ensure%20our%20fixes%20align%20closely%20with%20the%20freezing%2C%20or%20slow%2C%20or%20not%20responding%20issues%20you%20experience.%20Hence%20please%20stay%20connected%20to%20Excel%20and%20its%20community%2C%20read%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fblogs.office.com%2Fen-us%2Fexcel%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EExcel%20blog%20posts%3C%2FA%3E%3C%2FSPAN%3E%2C%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fhow-do-i-give-feedback-on-microsoft-office-2b102d44-b43f-4dd2-9ff4-23cf144cfb11%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esend%20us%20a%20frown%3C%2FA%3E%2C%20and%20send%20us%20ideas%20and%20suggestions%20for%20the%20next%20version%20of%20Excel%20through%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EUserVoice%3C%2FA%3E%3C%2FSPAN%3E.%20You%20can%20also%20follow%20Excel%20on%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.facebook.com%2Fmicrosoftexcel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFacebook%3C%2FA%3E%3C%2FSPAN%3E%20and%20%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftwitter.com%2Fmsexcel%3Fref_src%3Dtwsrc%255egoogle%257ctwcamp%255eserp%257ctwgr%255eauthor%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ETwitter%3C%2FA%3E%3C%2FSPAN%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPrash%20Shirolkar%3C%2FP%3E%0A%3CP%3EProgram%20Manager%2C%20Excel%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSUP%3E1%20%3C%2FSUP%3EAvailable%20in%20monthly%20channel%20version%201809%20or%20later%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-254199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3EThis%20is%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Eour%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Esecond%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Ewave%20of%20Excel%20O365%20performance%20improvements%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Ea%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Efter%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Eour%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22FieldRange%20SCXW230159973%22%3E%3CSPAN%20class%3D%22TextRun%20Underlined%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Efirst%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Underlined%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3ESept%20%E2%80%9917%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20Underlined%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Eone%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3E%2C%20to%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Ereinforce%20our%20commitment%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Eto%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Efixing%20top%20impacting%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Efreezing%2C%20slow%20and%20not%20responding%20performance%20issues%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Ederived%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Efrom%20Excel%20user%20feedback%20and%20usage%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Elearnings%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3E.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3EThis%20time%20around%20we%20have%20even%20more%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Eimprovements%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Ethan%20before%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22TextRun%20SCXW230159973%22%3E%3CSPAN%20class%3D%22NormalTextRun%20SCXW230159973%22%3Estarting%20with...%3C%2FSPAN%3E%3C%2FSPAN%3E%3CIMG%20style%3D%22float%3A%20left%3B%20padding%3A%200%2020px%2020px%200%3B%22%20src%3D%22https%3A%2F%2F8gportalvhdsf9v440s15hrt.blob.core.windows.net%2Flithium%2FExcel%2FSpeedy-LOOKUP-final_small.gif%22%20border%3D%220%22%20width%3D%22525%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-254199%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBlog%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-375742%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-375742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15804%22%20target%3D%22_blank%22%3E%40Prash%20Shirolkar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20on%20Office%20Insider%20Ver%201904%20Build%2011504.20000%3C%2FP%3E%3CP%3EI%20don't%20see%20the%20speed%20improvements%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-375724%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-375724%22%20slang%3D%22en-US%22%3E%3CP%3EUpdates...%3C%2FP%3E%0A%3CP%3EHey%20Sameer%20-%20you%20should%20see%20the%20VLOOKUP%20speedup%20in%20Insider%20Fast%20too...%20please%20let%20me%20know%20if%20you%20do%20not.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanx%20SynchronizerExcelCompareTool!%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%20Oscar%20-%20our%20plan%20is%20to%20enable%20for%20MacOS%20too.%20We%20are%20monitoring%20inner%20audience%20rings%20for%20MacOS%20currently...%20will%20keep%20you%20posted%20when%20we%20release%20to%20Production.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethanx%2C%3C%2FP%3E%0A%3CP%3EPrash%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353219%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353219%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20this%20available%20only%20on%20windows%20or%20on%20macOS%20as%20well%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309569%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3ENice%20work%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15804%22%20target%3D%22_blank%22%3E%40Prash%20Shirolkar%3C%2FA%3E%3CSPAN%3E.%20Thanks%20for%20sharing%20valuable%20information.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309013%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309013%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%204%20type%20packing%20in%20my%20products%201%20main%20packing%20and%203%20sub%20packing%3C%2FP%3E%3CP%3Emain%20pack%20is%20%3DBag%3C%2FP%3E%3CP%3E2nd%20pack%20is%20%3DPkt%3C%2FP%3E%3CP%3E3rd%20pack%20is%20%3DStrip%3C%2FP%3E%3CP%3E4th%20pack%20is%20%3DPcs%3C%2FP%3E%3CP%3Ei%20want%20formula%20for%20get%20result%20in%20all%20qty%3C%2FP%3E%3CP%3Ein%20cell%20G4%26nbsp%3B%20QTY%201%3C%2FP%3E%3CP%3Ein%20cell%20H4%20QTY%202%3C%2FP%3E%3CP%3Ein%20cell%20I4%20QTY%203%3C%2FP%3E%3CP%3Ein%20cell%20J4%20QTY%204%3C%2FP%3E%3CP%3Ein%20cell%20K4%20QTY%205%3C%2FP%3E%3CP%3Ein%20cell%20L4%20%3DG4%2BH4%2BI4%2BJ4%2BK4%3C%2FP%3E%3CP%3Ein%20cell%20M4%20is%20my%20main%20packing%20qty%20in%20pcs%20(pcs%20in%20main%20bag)%3C%2FP%3E%3CP%3Ein%20cell%20N4%20is%20my%20Sub%20packing%20(pcs%20in%20Pkt)%3C%2FP%3E%3CP%3Ein%20cell%20O4%20is%20my%20sub%2Fsub%20packing%20(Strip%20in%20pkt)%3C%2FP%3E%3CP%3Ein%20cell%20P4%20is%20my%20sub%2Fsub%2Fsub%20Packing%20(pcs%20in%20Strip)%3C%2FP%3E%3CP%3Ei%20know%20formula%20for%203%20cell%20but%20i%20want%20formula%20for%204%20cell%3C%2FP%3E%3CP%3Ei%20used%20below%20formula%20now%20in%203%20cell%3C%2FP%3E%3CP%3ED%3DTRIM(IF(L4%26gt%3B%3DN4%2CINT(L4%2FN4)%26amp%3B%22%20Pkt%20%22%2C%22%22))%3C%2FP%3E%3CP%3EE%3DIF(MOD(L4%2CN4)%26gt%3B%3DP4%2CINT(MOD(L4%2CN4)%2FP4)%26amp%3B%20%22%20Strip%20%22%2C%22%22)%3C%2FP%3E%3CP%3EF%3D%3DIF(MOD(L4%2CP4)%2CINT(MOD(L4%2CP4))%26amp%3B%22%20Pcs%22%2C%22%22)%3C%2FP%3E%3CP%3Ei%20want%20forumal%20for%20cell%20C%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e%3C%2FP%3E%3CP%3EG4%3D270%3C%2FP%3E%3CP%3EH4%3D90%3C%2FP%3E%3CP%3EI4%3D540%3C%2FP%3E%3CP%3EJ4%3D105%3C%2FP%3E%3CP%3EK4%3D30%3C%2FP%3E%3CP%3EL4%3DG4%2BH4%2BI4%2BJ4%2BK4%3D1035%3C%2FP%3E%3CP%3EM4%3D600PCS%3C%2FP%3E%3CP%3EN4%3D40PCS%3C%2FP%3E%3CP%3EO4%3D4%20STRIP%3C%2FP%3E%3CP%3EP4%3D10PCS%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERESULT%20IS%3C%2FP%3E%3CP%3EC4%3D1%20BAG%3C%2FP%3E%3CP%3ED4%3D10%20PKT%3C%2FP%3E%3CP%3EE4%3D3%20STRIP%3C%2FP%3E%3CP%3EF4%3D5PCS%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20HAVE%20FORMULA%20FOR%20D4%2CE4%2CF4%20BUT%20I%20DONT%20HAVE%20FORMULA%20TO%20GET%20RESULT%20IN%20C4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPLEASE%20GIVE%20ME%20FORMULA%20FOR%20CELL%20C4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-286076%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-286076%22%20slang%3D%22en-US%22%3E%3CP%3EThanx%20for%20the%20comments%20folks!%20Keep%20them%20coming.%3C%2FP%3E%0A%3CP%3ELooks%20like%20there%20may%20be%203%20outstanding%20issues%20left.%20Responding%20in%20order%20of%20when%20the%20comments%20were%20posted...%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EThanx%20Jeffrey!%20Btw%2C%20unless%20I%20am%20missing%20something%2C%20our%20accelerated%20EXACT%20VLOOKUP%20search%20with%20Speedy%20Lookup%20applies%20for%20both%20sorted%20or%20unsorted%20data...%20and%20so%20a%20user%20shouldn't%20have%20to%20resort%20to%20specifying%20a%20new%20-1%20option%20that%20you%20propose.%20Also%2C%20I%20was%20unable%20to%20reproduce%20the%208-10%20second%20delay%20in%20latest%20Monthly%20channel%3B%20when%20deleting%20the%20rows%20with%20Color%20%3D%20Red%20in%20the%20unsorted%20sheet%20in%20the%20workbook%20attached%20to%20the%20article.%20Can%20you%20give%20it%20a%20try%20too%20with%20the%20latest%20monthly%20channel%20and%20LMK%20if%20it%20still%20repros%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%0A%3CLI%3EThanx%20for%20confirming%20the%20speed-ups%20Dany!%20And%20thanx%20for%20the%20multi-threaded%20recalc%20in%20Online%20reminder.%20It%20is%20something%20on%20our%20TODO%20list.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%0A%3CLI%3EThanx%20for%20reaching%20out%20Sameer!%20You%20are%20correct%20Insider%20Fast%20audience%20doesn't%20experience%20lookup%20function%20speed%20up.%20Sorry%20for%20the%20inconvenience.%20Please%20refer%20to%20details%20in%20%3CA%20title%3D%22Windows%20Insider%20Fast%20-%20Speedy-lookup%20in%20Excel%20Information%22%20href%3D%22https%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fmsoffice_officeinsider-mso_win10-msoinsider_excel%2Fwindows-insider-fast-speedy-lookup-in-excel%2F132a1545-240d-43ef-9e87-7fdee89940c1%3Ftm%3D1541799849302%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Earticle%20here%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%3CBR%20%2F%3EHTH%3CBR%20%2F%3EPrash%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279000%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279000%22%20slang%3D%22en-US%22%3E%3CP%3EFast%26nbsp%3BMatch%20%2F%20Lookups%20are%20available%20only%20for%20the%20Monthly%20Channel%20(Targeted)%20-%20Build%201810%20(11001.20074)%3C%2FP%3E%3CP%3EThey%20are%20not%20available%20for%20Insider%20Build%201811%20(11022.200000)%3C%2FP%3E%3CP%3EIntentional%20or%20oversight%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277130%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277130%22%20slang%3D%22en-US%22%3E%3CP%3EPartially%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232722%22%20target%3D%22_blank%22%3E%40Miguel%20Ulloa%3C%2FA%3E%26nbsp%3B-%20In%20your%20Power%20BI%20portal%2C%20click%20the%20download%20arrow%20in%20the%20upper%20right%2C%20then%20%22Analyze%20in%20Excel%20Updates.%22%20It%20will%20guide%20you%20to%20a%20download%20that%20will%20install%20a%20set%20of%20files%20on%20your%20PC%20that%20will%20allow%20Excel%20to%20connect%20directly%20to%20your%20Power%20BI%20reports.%20It%20honors%20row%20level%20security%20in%20the%20service.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fpower-bi%2Fservice-analyze-in-excel%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EA%20full%20overview%20is%20here%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277112%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%26nbsp%3BIs%20this%20enabled%26nbsp%3B%20by%20the%20%22Power%20BI%20publisher%22%20thing%20I%20just%20found%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277104%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527%22%20target%3D%22_blank%22%3E%40Ed%20Hansberry%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20reply%20Ed.%20Both%20Power%20BI%20and%20Power%20Pivot%20include%20the%20username()%20function.%20In%20Power%20BI%2C%20I%20can%20use%20it%20in%20a%20DAX%20function%20that%20limits%20what%20the%20user%20sees%2C%26nbsp%3Bregardless%20of%20what's%20present%20in%20the%20file.%20How%20it%20works%20in%20in%20Power%26nbsp%3BBI%20is%20exactly%20how%20I'd%20like%20it%20to%20work.%20If%20it%20needs%20the%20user%20to%20be%20authenticated%2C%20then%20even%20better%2C%20I'd%20say.%20This%20is%20on%20a%20O365%20environment%2C%20so%20every%20user%20would%20be%20on%20the%20company%20network%20and%20properly%20authenticated.%20I%20realize%20the%20roles%20allow%20stuff%20like%20writing%26nbsp%3Bback%20to%20the%20model%2C%20but%20at%20the%20moment%2C%20it's%20only%20limiting%20what%20the%20user%20sees%20what%20I%20think%20it's%20missing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20research%20on%20this%2C%20I%20haven't%20seen%20your%20suggestion%2C%20I'll%20try%20to%20give%20it%20a%20shot%20and%20see%20what%20comes%20out%20of%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277001%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232722%22%20target%3D%22_blank%22%3E%40Miguel%20Ulloa%3C%2FA%3E%26nbsp%3B-%20how%20would%20roles%20work%20in%20Power%20Pivot%3F%20In%20Power%20BI%2C%20the%20service%20provides%20the%20authentication%20security%20to%20know%20what%20data%20to%20provide.%20There%20is%20no%20authentication%20needed%20for%20Excel.%20None%20possible%20in%20fact.%20You%20can%20get%20the%20currently%20logged%20in%20user%20with%20VBA%20code%2C%20but%20it%20is%20easy%20to%20bypass%20that%20by%20simply%20not%20running%20the%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20get%20roles%20in%20Excel%20by%20using%20Power%20BI%20as%20the%20source%20then%20connecting%20Excel%20to%20the%20Power%20BI%20data%20source.%20It%20is%20the%20%22Analyze%20in%20Excel%22%20feature.%20It%20adds%20a%20Power%20BI%20connector%20ribbon%20to%20Excel%2C%20and%20the%20addin%20has%20a%20login%20feature%20that%20the%20Power%20BI%20service%20authenticates.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-276975%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-276975%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20great%20news!%20I'd%20like%20to%20know%20if%20there%20are%20any%20plans%20to%20include%20the%20%22roles%22%20functionality%20to%20Power%20Pivot%2C%20as%20it%20is%20in%20Power%20BI.%20If%20not%2C%20I've%20opened%20a%20%3CA%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2Fforums%2F304921-excel-for-windows-desktop-application%2Fsuggestions%2F35797999-add-roles-to-power-pivot%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esuggestion%3C%2FA%3E%20on%20Excel's%20user%20voice.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-270943%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-270943%22%20slang%3D%22en-US%22%3E%3CP%3EGreat%20work%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15804%22%20target%3D%22_blank%22%3E%40Prash%20Shirolkar%3C%2FA%3E%26nbsp%3Band%20team.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-269115%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-269115%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%20hang%20upon%20opening%20after%20update%20to%20latest%20updates%2010%2F10%2F2018.%20Version%201809%20(Build%2010827.20150)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-262586%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-262586%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20version%201808%20on%20O365%20and%20it%20seems%20to%20be%20working%20for%20me%20already%3C%2FP%3E%3CP%3EI%20compared%204%20VLOOKUPs%20on%201%2C000%2C000%20rows%20against%20a%20table%20with%2018%2C000%20rows%3C%2FP%3E%3CP%3EIt%20took%2012%20CPU%20seconds%20using%20VLOOKUP%20and%20the%20same%20time%20using%20one%20column%20searching%20with%20MATCH%20and%204%20columns%20using%20INDEX.%3C%2FP%3E%3CP%3EWith%20the%20previously%20implementation%20the%20version%20using%20INDEX%20should%20be%204%20times%20faster%20but%20now%20it%20takes%20the%20same%20time.%3C%2FP%3E%3CP%3EOn%20Excel%20online%20the%20difference%20should%20be%20more%20dramatic%20because%20a%20large%20number%20of%20VLOOKUPs%20is%20a%20best%20case%20for%20multi%20threaded%20calculation%20which%20is%20not%20available%20in%20Excel%20online.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-262545%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-262545%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20news.%20One%20thing%20I'd%20still%20like%20to%20see%20in%20regards%20to%20lookups%20is%20to%20give%20us%20the%20option%20to%20do%20an%20%3CEM%3Eexact%3C%2FEM%3E%20match%20binary%20search%2C%20as%20per%20my%20comment%20at%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fdailydoseofexcel.com%2Farchives%2F2015%2F04%2F23%2Fhow-much-faster-is-the-double-vlookup-trick%2F%23comment-763551%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fdailydoseofexcel.com%2Farchives%2F2015%2F04%2F23%2Fhow-much-faster-is-the-double-vlookup-trick%2F%23comment-763551%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20also%20be%20interested%20to%20hear%20about%20whether%20your%20performance%20enhancements%20extend%20to%20optimizing%20the%20deletion%20of%20rows%20within%20filtered%20tables.%20This%20is%20currently%20so%20slow%20on%20large%20Tables%20(i.e.%20minutes)%20that%20it%20is%20much%20faster%20to%20copy%20the%20rows%20you%20want%20to%20keep%20from%20a%20filtered%20Table%2C%20rather%20than%20trying%20to%20delete%20the%20rows%20you%20don't%20want.%20See%20%3CA%20href%3D%22https%3A%2F%2Fwww.excelcampus.com%2Ftips%2Fsort-data-before-deleting-rows%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelcampus.com%2Ftips%2Fsort-data-before-deleting-rows%2F%3C%2FA%3E%20for%20the%20kind%20of%20scenario%20I'm%20talking%20about%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-648708%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-648708%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15804%22%20target%3D%22_blank%22%3E%40Prash%20Shirolkar%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EThanks%20for%20the%20information%2C%20was%20good%20to%20meet%20you%20last%20week.%20What%20I%20really%20like%20about%20this%20blog%20is%20the%20detail%20you%20have%20gone%20into%20explaining%20what%20the%20problem%20was%20and%20what%20you%20have%20done%20to%20make%20things%20faster.%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20further%20targets%20for%20speed%20improvements%3F%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-714381%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-714381%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20any%20plan%20to%20look%20into%20the%20efficiency%20of%20the%20cube%20formula%20functions%20like%20cubevalue()%2C%20cubemember()%2C%20cubeset()%3F%20These%20functions%20still%20write%20out%20mdx%20formulas%20(poorly)%20against%20tabular%20cubes%20and%20then%20split%20their%20query%20into%20sequential%20mdx%20queries%20that%20return%20a%20limited%20number%20of%20cells.%20Most%20analysis%20services%20servers%20can%20handle%20a%20larger%20load%20of%20queries%2C%20could%20likely%20send%20them%20in%20parallel%2C%20and%20preferably%20send%20dax%20statements%20to%20tabular%20cubes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20be%20great%20to%20hear%20what%20the%20plan%20is%20for%20these%20functions!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765941%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765941%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20about%20the%20old%20LOOKUP%20function%3F%3C%2FP%3E%3CP%3EDoes%20it%20take%20advantage%20from%20this%20update!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766095%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766095%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20old%20LOOKUP%20function%20used%20a%20Binary%20search%20algorithm%20and%20was%20already%20blazing%20fast%20like%20the%201%20or%20-1%20options%20of%20MATCH%20or%20the%201%20option%20of%20VLOOKUP%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-777931%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-777931%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20are%20these%20performance%20updates%20only%20available%20in%20Office%20365%2C%20or%20can%20they%20be%20obtained%20via%20a%20volume%20license%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-829290%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-829290%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20just%20as%20Excel%201902%20is%20being%20rolled%20out%20in%20my%20organisation%2C%20I%20discovered%20a%20very%20nasty%20calculation%20bug%20which%20is%20having%20a%20major%20business%20impact.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20occurs%20when%20opening%20a%20file%20which%20was%20made%20with%20any%20previous%20Excel%20release%20(I%20tested%20it%20back%20to%20Excel%202010)%20up%20to%20Excel%20Version%201808%20on%20Windows%2010%20is%20now%20opened%20using%20Excel%201902.%20Some%20cells%20using%20a%20LOOKUP%20formula%20are%20returning%20a%20%23NA%20result%20straight%20after%20opening%20the%20file.%3C%2FP%3E%3CP%3EI%20also%20found%20a%20way%20to%20reproduce%20this%20in%20a%20new%20file%20%2F%20with%20new%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20reproduce%20on%20a%20new%20file%3A%3C%2FP%3E%3COL%3E%3CLI%3EPut%20value%20%22ABC%22%20in%20A1%20and%20in%20A2%3C%2FLI%3E%3CLI%3EPut%20the%20following%20formula%20in%20A3%3A%20%22%3DLOOKUP(2%2C1%2F(A1%3AA2%26lt%3B%26gt%3B%22%22)%2CA1%3AA2)%22%20--%26gt%3B%20%3CEM%3Ethe%20purpose%20of%20this%20formula%20is%20to%20repeat%20the%20last%20non%20blank%20item%20from%20the%20referred%20set%20of%20cells%3C%2FEM%3E%3C%2FLI%3E%3CLI%3EDrag%20this%20formula%20down%20from%20A3%20down%20to%20A10%3COL%3E%3CLI%3EYou%20will%20see%20that%20all%20cells%20from%20A3%20to%20A10%20repeat%20the%20value%20%22ABC%22%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3CLI%3EClear%20the%20content%20of%20one%20cell%20in%20this%20list%20(A7%20by%20instance)%3COL%3E%3CLI%3EYou%20will%20see%20that%20A8%2C%20A9%2C%20and%20A10%20still%20repeat%20the%20value%20%22ABC%22%20%3CEM%3E(which%20is%20the%20purpose%20of%20the%20formula)%3C%2FEM%3E%3C%2FLI%3E%3C%2FOL%3E%3C%2FLI%3E%3CLI%3EEdit%20the%20formula%20of%20the%20cell%20below%20the%20emptied%20one%20(A8%20in%20this%20case)%2C%20just%20click%20in%20the%20formula%20bar%2C%20and%20press%20%22Enter%22%20without%20changing%20anything%3C%2FLI%3E%3CLI%3EForce%20a%20workbook%20recalculation%20by%20pressing%20CTRL%2BALT%2BSHIFT%2BF9%3C%2FLI%3E%3CLI%3ECell%20A8%2C%20A9%20and%20A10%20now%20will%20all%20carry%20a%20%23N%2FA%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20procedure%20allows%20to%20reproduce%20from%20scratch%20in%20a%20new%20workbook%20using%20Excel%201902.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20the%20criticality%20of%20this%20calculation%20bug%20is%20that%20when%20opening%20a%20workbook%20which%20was%20saved%20using%201808%20(and%20make%20use%20of%20the%20formula%20described%20in%20step%20%232)%2C%20while%20it%20worked%20fine%20with%201808%2C%20once%20opened%20using%201902%20it%20throws%20errors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-829847%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-829847%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400449%22%20target%3D%22_blank%22%3E%40Sebastien_Spas%3C%2FA%3E%26nbsp%3Bshouldn't%20it%20throw%20an%20error%3F%20If%20A7%20is%20empty%2C%20then%20your%20array%20above%20A6%3AA7%20evaluates%20to%201%2F%7BTRUE%3BFALSE%7D%20and%20FALSE%20%3D%200%2C%20so%20you%20get%20a%20%23DIV%2F0!%20error%20when%20evaluating%20it%2C%20which%20causes%20the%20entire%20formula%20to%20ultimately%20error%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%20to%20be%20the%20bug%20is%20it%20takes%20a%20CTRL-ALT-SHIFT-F9%20to%20get%20the%20error%20to%20show%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20wondering%20if%20this%20is%20one%20of%20those%20obscure%20issues%20that%20the%20new%20dynamic%20array%20calcs%20is%20bubbling%20to%20the%20top.%20But%20not%20sure%20why%20you%20are%20seeing%20it%20in%201902%20unless%20some%20of%20the%20Dynamic%20Array%20logic%20is%20there%20without%20the%20full%20functionality.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-829947%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-829947%22%20slang%3D%22en-US%22%3E%3CP%3EI%20don%E2%80%99t%20know%20about%20%E2%80%9Cshould%E2%80%9D%2C%20but%20this%20function%20worked%20without%20throwing%20errors%20in%20the%20past%2010%20years%20versions%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20is%20when%20opening%20field%20saves%20with%20previous%20versions%20of%20Excel%20where%20this%20did%20not%20throw%20an%20error%2C%20it%20now%20does%20after%20opening%20the%20file.%20On%20complex%20spreadsheets%20(this%20is%20my%20use%20case)%2C%20it%20has%20a%20cascading%20effect%2C%20and%20due%20to%20several%20IFERROR%20functions%20in%20between%20returns%20wrong%20totals%20(when%20compared%20to%20all%20prior%20Excel%20versions).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-829965%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20performance%20improvements%20now%20take%20seconds%20running%20Lookup-type%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-829965%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20might%20post%20a%20link%20to%20your%20comment%20above%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FPreview-of-Dynamic-Arrays-in-Excel%2Fba-p%2F252944%22%20target%3D%22_self%22%3Edynamic%20array%20thread%20here%3C%2FA%3E.%20I%20suspect%20the%20two%20are%20related.%20There%20might%20be%20something%20pop%20up%20about%20implicit%20intersection%2C%20a%20topic%20I%20try%20to%20avoid%20discussing%20at%20all%20costs.%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Fhtml%2Fimages%2Femoticons%2Flaugh_40x40.gif%22%20alt%3D%22%3Alol%3A%22%20title%3D%22%3Alol%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

This is our second wave of Excel Office 365 performance improvements after our first Sept ’17 one, to reinforce our commitment to fixing top impacting freezing, slow and not responding performance issues derived from Excel user feedback and usage learnings. This time around we have even more improvements than before starting with...

 

Lookup functions.png

Lookup functions VLOOKUP, HLOOKUP, and MATCH are one of the most used functions in Excel. If you use them with exact match to find items in a table or range in Excel, and see it noticeably take time in seconds or minutes or more, chances are you'll now see very noticeable improvement in the speed at which you see results.

 

 

We made them faster by more efficiently finding the item you are looking up. We create an index on-demand when you first search of a range of cells and then reuse it in subsequent lookups from the same range, until data changes in the lookup range. For details refer to our updated Excel Performance and limit improvements whitepaper.

 

Also, we have improved the speed of many use cases involving operations like copy/paste, undo, conditional formatting, cell editing, cell selection, scrolling, filtering, file open, and programmability, such as:

 

  • Copying the entire column in a sheet and selecting the clipboard is faster now because we optimized our large memory allocations with a more efficient data structure.
  • Undoing pasted cells with conditional formatting was slow because a paste operation was inefficient in generating an undo record for every cell with conditional formatting and with its own changed priority. It now combines them more efficiently into one undo record with changed priorities for all in it.
  • Undoing inserting a new row with a copied range with merged cells is now faster because we optimized handing of merged cells during an undo operation.
  • Deleting one or more rows with merged cells is now more performant because we skip redundant calls to render the visible grid in the midst of the deletion operation.
  • Viewing filter drop down for a column with lots of cells with conditional formatting is now faster because we optimized the filter drop down algorithm to be more efficient computing highlight, unique, and duplicate values.
  • Scrolling in the visible sheet, after an operation wasn’t optimal, because we re-rendered (or at least evaluated) all rows top to bottom (including filtered rows) in the visible sheet. This can be expensive depending on the number of rows and our ability to calculate for animation related rendering. Our optimization introduced in our first wave of performance improvements
  • Scrolling, editing of cells, cell selection, filtering operations in the grid are much faster when lots of rows are filtered / hidden because our rendering is more optimized to intelligently defer expensive rendering calls until we have calculated last row in the visible range.
  • Slow transitioning to the next cell after editing a cell adjacent to a large table or range of cells with data, because after editing a cell adjacent to a large table or range of cells with data, we would generate a preview of all cells in that column to Flash Fill, which is on by default. Depending on the size of the adjacent table or range of cells and whether the cells contain additional metadata like conditional formatting, this can be a time consuming and expensive operation. We now, limit scope of Flash Fill preview to the visible area improving Excel responsiveness. 
  • When opening any workbook we searched all existing ribbon content to ensure every component of the ribbon renders correctly. We have now made open faster by more intelligently searching in common cases like when the ribbon item being searched for doesn’t exist. Also, during open we now skip updating MRU links synchronously and instead update them asynchronously after open. Finally, we scope the rendering of the grid to the visible grid area when opening workbooks with lots of wrapped text in locales like Japanese.
  • Opening a local workbook when using a 3rd party anti-virus software is faster because we are more intelligent in avoiding searching for the 3rd party anti-virus vendor registered for scanning when a scan is determined to be redundant.
  • We open simple CSV text files much faster by employing a more efficient memory allocation.
  • Flashing scrollbars and slower running VBA code (than Excel 2010) even when ScreenUpdating property is set to false. We have addressed this by ensuring scrollbars will no longer update when ScreenUpdating property is set to false (just like in Excel 2010). This in turn diverts CPU cycles away from redundant rendering of the scrollbars and to running user VBA code, improving code execution speed.
  • Programmatically selecting a cell, a range of cells, or a sheet using VBA is faster because we avoid redundant calls for updating a document’s upload status when running VBA macro code performing operations like selecting a cell, a range of cells, or a sheet.

Office 365 can help you be even more productive.1 Give it a try today!


Now we know we are not done with this second wave and there is more, that said we would really like to hear from you to ensure our fixes align closely with the freezing, or slow, or not responding issues you experience. Hence please stay connected to Excel and its community, read Excel blog posts, send us a frown, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.

 

Prash Shirolkar

Program Manager, Excel

 

1 Available in monthly channel version 1809 or later

25 Comments
Occasional Visitor

Good news. One thing I'd still like to see in regards to lookups is to give us the option to do an exact match binary search, as per my comment at

http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/#comment...

 

I'd also be interested to hear about whether your performance enhancements extend to optimizing the deletion of rows within filtered tables. This is currently so slow on large Tables (i.e. minutes) that it is much faster to copy the rows you want to keep from a filtered Table, rather than trying to delete the rows you don't want. See https://www.excelcampus.com/tips/sort-data-before-deleting-rows/ for the kind of scenario I'm talking about here.

Occasional Visitor

I'm using version 1808 on O365 and it seems to be working for me already

I compared 4 VLOOKUPs on 1,000,000 rows against a table with 18,000 rows

It took 12 CPU seconds using VLOOKUP and the same time using one column searching with MATCH and 4 columns using INDEX.

With the previously implementation the version using INDEX should be 4 times faster but now it takes the same time.

On Excel online the difference should be more dramatic because a large number of VLOOKUPs is a best case for multi threaded calculation which is not available in Excel online.

Regular Visitor

Excel hang upon opening after update to latest updates 10/10/2018. Version 1809 (Build 10827.20150)

Great work @Prash Shirolkar and team.  

Occasional Visitor

This is great news! I'd like to know if there are any plans to include the "roles" functionality to Power Pivot, as it is in Power BI. If not, I've opened a suggestion on Excel's user voice.

Regular Contributor

@Miguel Ulloa - how would roles work in Power Pivot? In Power BI, the service provides the authentication security to know what data to provide. There is no authentication needed for Excel. None possible in fact. You can get the currently logged in user with VBA code, but it is easy to bypass that by simply not running the VBA.

 

You can get roles in Excel by using Power BI as the source then connecting Excel to the Power BI data source. It is the "Analyze in Excel" feature. It adds a Power BI connector ribbon to Excel, and the addin has a login feature that the Power BI service authenticates.

Occasional Visitor

@Ed Hansberry Thanks for your reply Ed. Both Power BI and Power Pivot include the username() function. In Power BI, I can use it in a DAX function that limits what the user sees, regardless of what's present in the file. How it works in in Power BI is exactly how I'd like it to work. If it needs the user to be authenticated, then even better, I'd say. This is on a O365 environment, so every user would be on the company network and properly authenticated. I realize the roles allow stuff like writing back to the model, but at the moment, it's only limiting what the user sees what I think it's missing.

 

In my research on this, I haven't seen your suggestion, I'll try to give it a shot and see what comes out of it.

Occasional Visitor

@Ed Hansberry Is this enabled  by the "Power BI publisher" thing I just found?

Regular Contributor

Partially @Miguel Ulloa - In your Power BI portal, click the download arrow in the upper right, then "Analyze in Excel Updates." It will guide you to a download that will install a set of files on your PC that will allow Excel to connect directly to your Power BI reports. It honors row level security in the service. A full overview is here.

Senior Member

Fast Match / Lookups are available only for the Monthly Channel (Targeted) - Build 1810 (11001.20074)

They are not available for Insider Build 1811 (11022.200000)

Intentional or oversight ?

 

Regards

Sam

Microsoft

Thanx for the comments folks! Keep them coming.

Looks like there may be 3 outstanding issues left. Responding in order of when the comments were posted...

  1. Thanx Jeffrey! Btw, unless I am missing something, our accelerated EXACT VLOOKUP search with Speedy Lookup applies for both sorted or unsorted data... and so a user shouldn't have to resort to specifying a new -1 option that you propose. Also, I was unable to reproduce the 8-10 second delay in latest Monthly channel; when deleting the rows with Color = Red in the unsorted sheet in the workbook attached to the article. Can you give it a try too with the latest monthly channel and LMK if it still repros?

  2. Thanx for confirming the speed-ups Dany! And thanx for the multi-threaded recalc in Online reminder. It is something on our TODO list.

  3. Thanx for reaching out Sameer! You are correct Insider Fast audience doesn't experience lookup function speed up. Sorry for the inconvenience. Please refer to details in article here


HTH
Prash

Occasional Visitor

I have 4 type packing in my products 1 main packing and 3 sub packing

main pack is =Bag

2nd pack is =Pkt

3rd pack is =Strip

4th pack is =Pcs

i want formula for get result in all qty

in cell G4  QTY 1

in cell H4 QTY 2

in cell I4 QTY 3

in cell J4 QTY 4

in cell K4 QTY 5

in cell L4 =G4+H4+I4+J4+K4

in cell M4 is my main packing qty in pcs (pcs in main bag)

in cell N4 is my Sub packing (pcs in Pkt)

in cell O4 is my sub/sub packing (Strip in pkt)

in cell P4 is my sub/sub/sub Packing (pcs in Strip)

i know formula for 3 cell but i want formula for 4 cell

i used below formula now in 3 cell

D=TRIM(IF(L4>=N4,INT(L4/N4)&" Pkt ",""))

E=IF(MOD(L4,N4)>=P4,INT(MOD(L4,N4)/P4)& " Strip ","")

F==IF(MOD(L4,P4),INT(MOD(L4,P4))&" Pcs","")

i want forumal for cell C=

 

i.e

G4=270

H4=90

I4=540

J4=105

K4=30

L4=G4+H4+I4+J4+K4=1035

M4=600PCS

N4=40PCS

O4=4 STRIP

P4=10PCS

 

RESULT IS

C4=1 BAG

D4=10 PKT

E4=3 STRIP

F4=5PCS

 

I HAVE FORMULA FOR D4,E4,F4 BUT I DONT HAVE FORMULA TO GET RESULT IN C4

 

PLEASE GIVE ME FORMULA FOR CELL C4

 

 

Nice work @Prash Shirolkar. Thanks for sharing valuable information.

Occasional Visitor

Is this available only on windows or on macOS as well?

Microsoft

Updates...

Hey Sameer - you should see the VLOOKUP speedup in Insider Fast too... please let me know if you do not. 

Thanx SynchronizerExcelCompareTool! 

Also Oscar - our plan is to enable for MacOS too. We are monitoring inner audience rings for MacOS currently... will keep you posted when we release to Production. 

 

thanx,

Prash

Senior Member

@Prash Shirolkar 

I am on Office Insider Ver 1904 Build 11504.20000

I don't see the speed improvements

 

Sam

New Contributor

Hi @Prash Shirolkar ,

Thanks for the information, was good to meet you last week. What I really like about this blog is the detail you have gone into explaining what the problem was and what you have done to make things faster.

Do you have any further targets for speed improvements?

Regards

New Contributor

Is there any plan to look into the efficiency of the cube formula functions like cubevalue(), cubemember(), cubeset()? These functions still write out mdx formulas (poorly) against tabular cubes and then split their query into sequential mdx queries that return a limited number of cells. Most analysis services servers can handle a larger load of queries, could likely send them in parallel, and preferably send dax statements to tabular cubes.

 

Would be great to hear what the plan is for these functions!

Trusted Contributor

What about the old LOOKUP function?

Does it take advantage from this update!

Established Member

The old LOOKUP function used a Binary search algorithm and was already blazing fast like the 1 or -1 options of MATCH or the 1 option of VLOOKUP

Occasional Visitor

Hi, are these performance updates only available in Office 365, or can they be obtained via a volume license?

Frequent Visitor

Hi, just as Excel 1902 is being rolled out in my organisation, I discovered a very nasty calculation bug which is having a major business impact.

 

This occurs when opening a file which was made with any previous Excel release (I tested it back to Excel 2010) up to Excel Version 1808 on Windows 10 is now opened using Excel 1902. Some cells using a LOOKUP formula are returning a #NA result straight after opening the file.

I also found a way to reproduce this in a new file / with new cells.

 

How to reproduce on a new file:

  1. Put value "ABC" in A1 and in A2
  2. Put the following formula in A3: "=LOOKUP(2,1/(A1:A2<>""),A1:A2)" --> the purpose of this formula is to repeat the last non blank item from the referred set of cells
  3. Drag this formula down from A3 down to A10
    1. You will see that all cells from A3 to A10 repeat the value "ABC"
  4. Clear the content of one cell in this list (A7 by instance)
    1. You will see that A8, A9, and A10 still repeat the value "ABC" (which is the purpose of the formula)
  5. Edit the formula of the cell below the emptied one (A8 in this case), just click in the formula bar, and press "Enter" without changing anything
  6. Force a workbook recalculation by pressing CTRL+ALT+SHIFT+F9
  7. Cell A8, A9 and A10 now will all carry a #N/A

 

This procedure allows to reproduce from scratch in a new workbook using Excel 1902.

 

However the criticality of this calculation bug is that when opening a workbook which was saved using 1808 (and make use of the formula described in step #2), while it worked fine with 1808, once opened using 1902 it throws errors.

 

Contributor

@Sebastien_Spas shouldn't it throw an error? If A7 is empty, then your array above A6:A7 evaluates to 1/{TRUE;FALSE} and FALSE = 0, so you get a #DIV/0! error when evaluating it, which causes the entire formula to ultimately error out.

 

Seems to be the bug is it takes a CTRL-ALT-SHIFT-F9 to get the error to show up.

 

I am wondering if this is one of those obscure issues that the new dynamic array calcs is bubbling to the top. But not sure why you are seeing it in 1902 unless some of the Dynamic Array logic is there without the full functionality.

Frequent Visitor

I don’t know about “should”, but this function worked without throwing errors in the past 10 years versions of Excel.

 

The issue is when opening field saves with previous versions of Excel where this did not throw an error, it now does after opening the file. On complex spreadsheets (this is my use case), it has a cascading effect, and due to several IFERROR functions in between returns wrong totals (when compared to all prior Excel versions).

Contributor

You might post a link to your comment above in the dynamic array thread here. I suspect the two are related. There might be something pop up about implicit intersection, a topic I try to avoid discussing at all costs.