# Thread: My VLOOKUP is showing a lot of #N/A entries when no match. Cant it just return blank?

1. ## My VLOOKUP is showing a lot of #N/A entries when no match. Cant it just return blank?

I have a VLOOKUP that works fine when there is match, but when there isnt it shows a value of #N/A in the cell. Is there any way to not show the ugly #N/A value?
My lookup table is sorted, and I am matching on email address.

See screen shot attached.

Also when there is a match, if the lookup cell has a blank in it, for some reason it is returning a 0.

2. Try

=IFERROR(IF(VLOOKUP(\$A5,Results!\$C\$1:\$)(\$50,3,False))=0,"",VLOOKUP(\$A5,Results!\$C\$1:\$)(\$50,3,False)),"")

3. Thanks Bob. I will try that out tomorrow!
Much appreciated.

4. Originally Posted by cruttley
Thanks Bob. I will try that out tomorrow!
Much appreciated.
I had to tweak the formula a bit to get it to work....but all is well now and now I understand the IFERROR function.

=IFERROR(IF(VLOOKUP(\$A5,Results!\$C\$1:\$O\$50,3,FALSE)=0,"",VLOOKUP(\$A5,Results!\$C\$1:\$O\$50,3,FALSE)),"")

5. Goodness, I don't know what happened there (let's just pretend I did it deliberately to get you to think about it and understand it - you passed with flying colours ). Glad you sussed it.

#### Posting Permissions

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