+ Reply to Thread
Results 1 to 4 of 4

Slow Array formula

  1. #1
    Forum Contributor
    Join Date
    05-27-2014
    Posts
    141

    Slow Array formula

    Hi All,

    I have a spreadsheet with a set of arrays that lookup two sets of information to find the matching data, but it is very laggy is there anything I can do to speed it up??

    Please Login or Register  to view this content.
    I have attached the sheet and highlighted in Yellow the relevant fields

    Many thanks for any input

    Danny
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Slow Array formula

    Slow? Of course!. Do not use open ranges. Use something like B2:B100 or whatever your range is.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    05-27-2014
    Posts
    141

    Re: Slow Array formula

    I feel like a lemon, I did not notice that and Ive been raking my brains for ages.

    Cheers

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Slow Array formula

    Indeed! It's a shame that the idea that you shouldn't reference entire columns in array formulas is not more widely known. It's amazing how many people, even some quite proficient users, are unaware of this.

    Perhaps the confusion arises from the apparently inconsistent view that Microsoft have adopted here: it's well known that functions such as COUNTIF(S)/SUMIF(S) suffer no loss in performance using entire column references, so the assumption that it's also ok in other, array constructions is quite a natural one.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Array formula too slow. Need an alternative..
    By cool_anu4u in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2013, 04:56 AM
  2. [SOLVED] Slow index/small/row array
    By Jovica in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-06-2012, 06:59 AM
  3. Can Array Formulas slow down XL?
    By Salomey in forum Excel General
    Replies: 3
    Last Post: 09-24-2010, 11:53 AM
  4. Slow array-formulas
    By Jonathan78 in forum Excel General
    Replies: 6
    Last Post: 08-25-2009, 02:04 AM
  5. [SOLVED] MY ARRAY FORMULA IS SLOW IF DATA LIST IS MORE THAN 10000R0WS
    By S.DURAIVEL - ABU DHABI in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2005, 05:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1