@ > Home > Contents > Calculate Lookup inside IF(ISERROR(VLOOKUP( once only Excel 97+


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:

xl2003: =IF(ISERROR(VLOOKUP("X",A1:B9999,2,)),"none",VLOOKUP("X",A1:B9999,2,))
xl2007: =IFERROR(VLOOKUP("X",A1:B9999,2,),"none")

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.