+ Reply to Thread
Results 1 to 5 of 5

Help to simplify an =IF(IFERROR(INDEX(...,MATCH( formula

  1. #1
    Registered User
    Join Date
    03-11-2014
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    5

    Help to simplify an =IF(IFERROR(INDEX(...,MATCH( formula

    Hi all,

    Have used this forum a lot in the past so thanks for all the help up to this point!

    I have had to make a pretty huge formula in an attempt to automate an existing report (unfortunately I can't change the format of the report), which is working but takes incredibly long to update (10 minutes plus on a Core i5).

    The formula matches a date and a meeting room and returns how many hours that room was booked out for for the day. The complicating factor is that if the room was booked out for more than 8 hours, the formula needs to return 8 hours, and there is another formula that will return the number of hours in excess of 8. This is the first formula (returning a max of 8 hours):

    =IF(IFERROR(INDEX(ImportedData!$F:$F,MATCH($D21&$F$19,ImportedData!$E:$E&ImportedData!$B:$B,0)),0)+IFERROR(INDEX(ImportedData!$D:$D,MATCH($F$19&$D21,ImportedData!$B:$B&ImportedData!$A:$A,0)),0)>8,8,(IFERROR(INDEX(ImportedData!$F:$F,MATCH($D21&$F$19,ImportedData!$E:$E&ImportedData!$B:$B,0)),0))+(IFERROR(INDEX(ImportedData!$D:$D,MATCH($F$19&$D21,ImportedData!$B:$B&ImportedData!$A:$A,0)),0)))

    And this is the second (returning anything above 8 hours)

    =IFERROR(IF(INDEX(ImportedData!$F:$F,MATCH($D53&$F$19,ImportedData!$E:$E&ImportedData!$B:$B,0))+(INDEX(ImportedData!$D:$D,MATCH($F$19&$D53,ImportedData!$B:$B&ImportedData!$A:$A,0)))>8, (INDEX(ImportedData!$F:$F,MATCH($D53&$F$19,ImportedData!$E:$E&ImportedData!$B:$B,0)))+(INDEX(ImportedData!$D:$D,MATCH($F$19&$D53,ImportedData!$B:$B&ImportedData!$A:$A,0)))-8,0),0)

    Is there any way that I can do this is a more efficient manner to make it quicker to update? I can't think of how to do this in VBA, or of how I can make the formula more efficient without modifying the report (which again I can't really do).

    I've attached part of the report (heavily butchered to save on size and protect info), if you look at this, don't worry so much about the other broken formulas (I can fix these). Also, I'm not sure if the formulas will auto update or not now that I've sent this.

    I know this a big ask, so thanks heaps for any time you put into this.

    Regards,

    Stuart
    Attached Files Attached Files

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

    Re: Help to simplify an =IF(IFERROR(INDEX(...,MATCH( formula

    Hi and welcome to the forum!

    Your main (and major) issue is the use of entire column references in your formula combined with array-entry. Replicating your formula in a workbook took me over three seconds just to calculate a single cell, which is an astonishingly long time.

    It's not so much an issue in the INDEX part, though, since you're using concatenation of two fields on two occasions (E:E&B:B and B:B&A:A) for the MATCH range, this means that you are having to commit this as an array formula. This, combined with the whole column references, is causing Excel to calculate way beyond what is necessary.

    Firstly, I would add two helper columns to your data table which perform the concatenation of these column values and reference those in the formula instead. That way, no array-entry is required.

    Secondly, your first formula can be reduced to (where I've also implemented those changes, putting the concatenated column for E&B as G and that for B&A as H):

    =MIN(8,IFERROR(INDEX(ImportedData!$F:$F,MATCH($D21&$F$19,ImportedData!$G:$G,0)),0)+IFERROR(INDEX(ImportedData!$D:$D,MATCH($F$19&$D21,ImportedData!$H:$H,0)),0))

    No array-entry required, and should be much, much faster.

    Regards
    Click * below if this answer helped

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

  3. #3
    Registered User
    Join Date
    03-11-2014
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help to simplify an =IF(IFERROR(INDEX(...,MATCH( formula

    Thank you so much, this has helped me out immensely.

    At the bottom of your post it says to press the * if the post was helpful, which it was but I don't know what button to press?

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

    Re: Help to simplify an =IF(IFERROR(INDEX(...,MATCH( formula

    You're welcome!

    (It's Add Reputation, if you really want )

    Regards

  5. #5
    Registered User
    Join Date
    03-11-2014
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help to simplify an =IF(IFERROR(INDEX(...,MATCH( formula

    Ah there it is! I have added rep. Thanks again =)

+ 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] IFERROR+INDEX+MATCH Formula is not returning the correct value
    By bxk006 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-16-2013, 09:50 AM
  2. Wrong result displaying using IFERROR-INDEX-MATCH-ROW Formula
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2013, 01:03 PM
  3. comparision between two excel sheet using VBA Code for Index/match/and/iferror formula
    By gaurangaero in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2012, 10:08 AM
  4. Looking for better formula of iferror, index and match
    By DavidRoger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-23-2012, 07:50 AM
  5. Custom function to simplify Index(match)) formula
    By Martin in forum Excel General
    Replies: 0
    Last Post: 03-20-2006, 10:50 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