Prevent #N/A! in Excel VLOOKUP

September 10, 2014 - by Bill Jelen

So, you have this nice little =VLOOKUP(A2,$Z$2:$AB$99,3,False) formula. But in order to prevent the #N/A! error when something is not found, you used to have to do the VLOOKUP twice: =IF(ISNA(VLOOKUP(A2,$Z$2:$AB$99,3,False)),”Not Found”,VLOOKUP(A2,$Z$2:$AB$99,3,False)).

This is far simpler starting in Excel 2010:

Think about it. Let’s assume you are doing 1000 VLOOKUPs. There are 990 that are found and 10 that are not found. The old formula would end up doing 1990 VLOOKUPs. The new formula will do 1000 VLOOKUPs. It will be almost twice as fast.

This is one of the tips in Learn Excel 2007-2010 from MrExcel – 512 Excel Mysteries Solved.