Ask Your Question

Revision history [back]

The expression =IF(C3<>"",IF(ISERROR(VLOOKUP(C3,'Item List'.A:Z,2,0)),"New Item",VLOOKUP(C3,'Item List'.A:Z,2,0)),"") needs to execute the lookup twice if successful. You can likely half the time by using =IF(C3<>"",IFNA(VLOOKUP(C3,'Item List'.A:Z,2,0),"New Item"),"") instead.

Also note that ISERROR in your formula suppresses any error, while IFNA matches only the #N/A error as a no-match result of VLOOKUP. Otherwise you would have seen that if the sheet 'Item List' did not exist the result was #NAME?

Using the Function Wizard with VLOOKUP (or HLOOKUP or MATCH or LOOKUP) on large data can be problematic, as the Function Wizard needs to invoke not only the functions of the expression but also obtain the intermediate data arrays and results.