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

Problem

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.

Solutions

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,))
becomes
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.