Thanks:  0
Likes:  0

# Thread: Lookup formula error

1. 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.

2. 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.

3. 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

4. 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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•