Lookup formula error

[Perry M]

I have a form that I use that collects numerical data from several operations. I use lookups to bring that data into another worksheet. My problem is that not all values I search for are always present.
Is there a way to have the lookup return a "0" instead of the "#N/A" error if the value I am looking up is not present?
I thought that I had seen this posted here before, but I have been unable to find it.

 

[markbanin]

You can use:

=IF(ISNA(VLOOKUP($C$10,Suppliers!$A$2:$B$28,2,FALSE)),"",VLOOKUP($C$10,Suppliers!$A$2:$G$28,4,FALSE))

This is one that I use. If it gets an #N/A error it puts in "" (blank) otherwise it just puts the lookup in.

 

[MrExcel]

It is annoying, but the solution that I use is as follows:

Let's say your lookup is:
=VLOOKUP(A1,$Z$1:$AA$99,2,FALSE)

Then I would use
=IF(ISNA(VLOOKUP(A1,$Z$1:$AA$99,2,FALSE)),0,VLOOKUP(A1,$Z$1:$AA$99,2,FALSE))

The ISNA() function tells you if you are going to get an N/A. If you are, then the IF statement puts a zero, otherwise, it lets the VLOOKUP recalc.

Bill

 

[Asala42]

Another one:

=IF(countif($A$2:$A$28,C10)=0,"",VLOOKUP($C$10,Suppliers!$A$2:$G$28,4,FALSE))

This post has come up time to time

Adam