Stop The #N/A! Error in VLOOKUP and other Lookup Functions

1) Add your lookup formula to a spare column (e.g Column "A") and allow the
#N/A! to happen.

2) Now reference these cells from the required cells like this;

=IF(ISNA(A1),0,A1)

3) Hide Column "A" by selecting it and going to Format>Column>Hide

Instead of;

 =VLOOKUP("Dog",A1:D100,2,False)

Use;

=IF(ISNA(VLOOKUP("Dog",A1:D100,2,FALSE)),0,VLOOKUP("Dog",A1:D100,2,FALSE))

If you don't like the Zeros showing you can hide them via
Tools>Options>View - Zero values. Or, cell-by-cell with a
Custom Format like:
General;-General;

This content was originally posted on http://officeresources.blogspot.com/ © 2008 If you are not reading this text from the above site, you are reading a splog