Lookups (and some other calculations, too) need a lot of computing time if the count of lookup formulas is high, and/or the referenced lookup lists are long. At least unnecessary calculations, as for error treating reasons, should be avoided.
1) See the storage of a general variable for repeating formula parts, SETV/GETV, at 5001.htm.
2) xl2007 introduces =IFERROR() which according to 5001.htm works out as follows:
The missing backward compatibility between the xl12 (2007) and xl4 (2003) model is a problem if users before xl2007 have to open it.
3) Consider storing intermediate calculations in extra cells! This may sound "uncool"; nevertheless, you gain an optimum of cross-product-and-release-compatibility, understanding (self documentation), file size and speed.
But see this trick:
4) Bernd Plumhoff (2007/09/10) has a simple workaround combining advantages and omitting the contras:
Y1: =VLOOKUP(X1,A1:B10000,2,) does it all.
A B CDEFGHIJKLMNOPQRSTUVW X Y 1 Key1 Val1 Z-966 none 2 A-100 1 3 A-102 2 4 A-103 3 5 A-105 4 6 A-106 5 7 A-107 6 8 A-108 8 9 A-110 9 : 9998 Z-985 10 9999 Z-991 11 10000 =X1 none
He attaches a field linked to the input cell (or to the responsible cell for the lookup), here A10000: =X1. The lookup value is just another ordinary result. If the lookup does not succeed before, it now falls into this net of security.
Remember to set =VLOOKUP()'s fourth argument as FALSE, to ensure exact matches. However, also intervals (the normal setting which needs a sorted lookup) are possible. =HLOOKUP() works respectively. =MATCH() needs a different "error" definition as it returns a pointer instead of data.