+ Reply to Thread
Results 1 to 3 of 3

Index with large data

  1. #1
    Registered User
    Join Date
    04-26-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    1

    Index with large data

    Hi,

    I have a large group of data, 13 Columns x 154228 Rows. I have 6 index formulas embedded in my working spreadsheet, and it is tedious as it will take me at least 5-10 mins to return a set of 15 values! Sometimes, excel will display the error of "ran out of resources" and I am unable to proceed. Is there other formulas I can use? Or any suggestions?

    {=IFERROR(INDEX(SPREADSHEET1!D:M,MATCH(1,(B7=SPREADSHEET1!D:D)*("MASKED1"=SPREADSHEET1!J:J),0),8),"NOT FOUND")}
    {=IFERROR(VALUE(INDEX(SPREADSHEET1!D:M,MATCH(1,(B7=SPREADSHEET1!D:D)*("MASKED1"=SPREADSHEET1!J:J),0),10)),0)}

    {=IFERROR(INDEX(SPREADSHEET1!D:M,MATCH(1,(B7=SPREADSHEET1!D:D)*("MASKED2"=SPREADSHEET1!J:J),0),8),"NOT FOUND")}
    {=IFERROR(VALUE(INDEX(SPREADSHEET1!D:M,MATCH(1,(B7=SPREADSHEET1!D:D)*("MASKED2"=SPREADSHEET1!J:J),0),10)),0)}

    {=IFERROR(INDEX(SPREADSHEET1!D:M,MATCH(1,(B7=SPREADSHEET1!D:D)*("MASKED3"=SPREADSHEET1!J:J),0),8),"NOT FOUND")}
    {=IFERROR(VALUE(INDEX(SPREADSHEET1!D:M,MATCH(1,(B7=SPREADSHEET1!D:D)*("MASKED3"=SPREADSHEET1!J:J),0),10)),0)}

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,149

    Re: Index with large data

    Array formulas such as these on that amount of data will certainly cause some slow down.
    Perhaps if you post a sample workbook and explain what it is you're trying to do then someone will be able to assist with alternative formulas.

    BSB

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Index with large data

    You have 154228 rows of data and your formula refer to entire columns, that means around 85% of the processing effort is being put into empty rows!

    First formula example

    {=IFERROR(INDEX(SPREADSHEET1!K:K,MATCH(1,(B7=SPREADSHEET1!D1:D154228)*("MASKED1"=SPREADSHEET1!J1:J154228),0)),"NOT FOUND")}

    Having the full column in the index range will not make much difference, although I have reduced it to include only the relevant column instead of the entire table, but reducing the ranges in the arrays should provide a significant improvement.
    Last edited by jason.b75; 04-26-2016 at 08:40 AM.

+ 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] How to optimise INDEX(MATCH()) for large data sets?
    By Dan155 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2015, 11:03 PM
  2. [SOLVED] Index Match with multiple criteria and selecting data from a large table
    By Aquarock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2014, 09:26 PM
  3. [SOLVED] Index Match Large colum and row
    By embar in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-16-2012, 02:28 AM
  4. Excel 2007 : INDEX LARGE with two conditions.
    By jvelez198 in forum Excel General
    Replies: 2
    Last Post: 02-20-2012, 08:26 AM
  5. INDEX, MATCH and LARGE
    By ridebikes in forum Excel General
    Replies: 1
    Last Post: 10-24-2011, 04:03 PM
  6. Index, Match, and Large
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2006, 06:10 PM
  7. [SOLVED] LARGE and INDEX functions
    By bob in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-03-2006, 02:15 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