How to optimise IFISERROR

I have just taken on a workbook with many work sheets that all contain a very large number of VLOOKUP formulas containing IFISERROR in order to ensure there are no errors reported which will prevent the many calcuations from working correctly.

My issue is that the sheets contain many areas (forms) for input by the users and relies upon immediate update of information to be able to make various decisions and try multiple "what if" scenarios on the numbers, however the calculation of the workbook takes over three minutes and is really spoiling the "useability" of the tool the workbook is supposed to provide.

Is there an alternative to IFISERROR that will provide the same functionality but be less demanding when re-calculating?

Auto-calculation is already off as it rendered the sheet unusable.

The main issue is with timing as it takes over three minutes each time F9 is pressed and the purpose of the sheet is to allow multiple "what-if" scenarios for production planning purposes so it is not unusual for the users to want to run over a dozen "what-ifs" each time the sheet is used.

I remember seeing somewhere a reference to replacing ISERROR with some form of Excel database functionality but cannot find that reference now, which is very frustrating.

To give you more of an idea, there are over 3,000 VLOOKUP with IFISERROR lines within the workbook!

Originally Posted by AussieExcelUser

Is there an alternative to IFISERROR that will provide the same functionality but be less demanding when re-calculating?

Where ever you have the same formula in contiguous rows, you can use an array formula. Just select the entire range of rows where the same formula will be entered (by same formula, I mean same, but varies by the row or column), enter the formula in the first cell, and press Ctrl, Shift Enter.

An array formula is more efficient than the individual formulas.

Matt