I was reading Dealing with VLOOKUP and GETPIVOTDATA errors by Ken Puls and noticed he wraps VLOOKUP formulas in IF statements like this:
=IF(ISNA(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0, VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False))
The way it works is first, the code does the VLOOKUP and if the result is #N/A, it returns zero, otherwise it performs the VLOOKUP (again!) and returns the result.
The problem with this formula should be obvious: if your value exists, the VLOOKUP is performed twice! Imagine filling this formula down 10, 50, 100 columns and the resulting performance hit on your workbook.
A few of the commenters suggested alternatives which would work better. My favorite is the COUNTIF alternative:
=IF(COUNTIF(LeftmostColumn_of_rngToLookIn,rngToLookup),VLOOKUP(rngToLookup, rngToLookIn, ColToReturn, False), 0)
This formula uses COUNTIF to see if the value exists in the leftmost column (the column that VLOOKUP searches) and if it doesn't exist, it skips the VLOOKUP.
About JP
Follow Me