Avoid #N/A in Lookup Functions & Other Issues

  • Lookup Functions
    Excel is very rich in Lookup & Reference Formulae, with the most popular probably being VLOOKUP. These functions are all very generic and can be used to extract data from just about any table of data. The biggest mistake made by most, is the forcing of Excel to look in thousands, if not millions of cells superfluously. One of the best ways to overcome this is via the use of [dr]*[/dr]


    Speeding up the Calculations
    The other mistake is that the lookup functions (in the case of VLOOKUP, HLOOKUP and MATCH) are told to find an exact match. That is, the optional fourth argument Range_lookup is set to False in both VLOOKUP and HLOOKUP. In the case of MATCH the last optional argument (Match_type) is set to 0 (zero). This means that Excel will need to check all cells until it finds an exact match. If possible, always use True (or omitted) for VLOOKUP and HLOOKUP, or 1 (sort ascending) or -1 (sort descending) in the case of Match. So, whenever possible, sort your data appropriately and avoid the use of exact match. This will then mean Excel uses the much faster binary search is used


    The #N/A Issue
    Another very bad mistake is the double use of the Lookup Function nested within one of Excels Information functions. see example below


    =IF(ISNA(VLOOKUP(100,MyRange,2,False)),"",VLOOKUP(100,MyRange,2,False))


    This is used to prevent the #N/A when no match can be found. The big problem with this is, it forces Excel to use the VLOOKUP twice! As you can imagine, this doubles the number of Lookup functions used. The best approach (if possible) is to live with the #N/A, or hide it via Conditional Formatting. Or, if this is not an option, place the Lookup in a 'out-the-way' spot on the same Worksheet (eg IV1) and then use:


    =VLOOKUP(100,MyRange,2,False)


    Result Cell =IF(ISNA(IV1),"",IV1)


    This halves the number of Lookup functions needed!


    One other common problem is storing the Lookup Function on another sheet to the Table. While the effect of this is not too bad on approximate matches, it can be dramatic on exact matches. Consider placing the Lookup functions on the same Worksheet as the Table, then create a simple reference (eg =Sheet1!IV1) to the cell(s) to get the result into the needed Worksheet. Doing this also opens up another opportunity in that we could now use:


    =IF(ISNA(Sheet1!IV1),"",Sheet1!IV1)


    If you must use double formula to address the #N/A issue, opt for the slightly faster COUNTIF function to check the existence of the lookup value, e.g


    =IF(COUNTIF($A$1:$A$100,"cat"),VLOOKUP("cat",$A$1:$D$100,2,FALSE),"")


    Last, but far from least, learn how to use Excels Database functions. They are very easy to use and are often much faster than their Lookup & Reference counterpart