Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Click image for larger version. 

Name:	2014-03-19_17-34-56.jpg 
Views:	22 
Size:	60.8 KB 
ID:	2154

  2. #2
    Try

    =IFERROR(IF(VLOOKUP($A5,Results!$C$1:$)($50,3,False))=0,"",VLOOKUP($A5,Results!$C$1:$)($50,3,False)),"")
    Last edited by Bob Phillips; 2014-03-20 at 12:59 AM.

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

  4. #4

    Red face

    Quote Originally Posted by cruttley View Post
    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. #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
  •