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

1. ## 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  Register To Reply

2. ## 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  Register To Reply

3. ## 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?  Register To Reply

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

You're welcome!

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

Regards  Register To Reply

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

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