Results 1 to 8 of 8

Thread: Return a Blank if Error OR 0

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return a Blank if Error OR 0

    Hi guys,

    Wondering if anybody a help with the following:

    I am currently using the following formula to return blanks when an error results from 'E25' being blank:

    =IFERROR(VLOOKUP(E25,'WORKSHEET'!$A$1:$S$1000,19,FALSE,"")

    However, when E25 is not blank but row '19' in '$A$1:$S$1000' has no results, the formula returns a '0'.

    In the case of there being no result to return, I'd like there also to be a blank (and for there to only not be a blank when there is a result to return - 'YES or 'NO' being the only 2 returnable options in this case).

    I have tried OR and AND formulae but with no success.

    I'm tired so maybe I have missed something.

    Any help would be much appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Return a Blank if Error OR 0

    How about
    =IF(VLOOKUP(E25,WORKSHEET!$A$1:$S$1000,19,FALSE)=0,"",IFERROR(VLOOKUP(E25,WORKSHEET!$A$1:$S$1000,19,FALSE),""))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,671
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Return a Blank if Error OR 0

    Hi Fluff. That would error before the iferror ever had its chance. Maybe wrap the if rather than the vlookup.

    =IFERROR(IF(VLOOKUP(E25,WORKSHEET!$A$1:$S$1000,19,FALSE)=0,"",VLOOKUP(E25,WORKSHEET!$A$1:$S$1000,19,FALSE)),"")
    Looking for opportunities

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Return a Blank if Error OR 0

    Excellent point, I obviously wasn't thinking clearly
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,671
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Return a Blank if Error OR 0

    I forget to think all the time

    You could also do this if the lookup is producing text results:

    =IFERROR(""&VLOOKUP(E25,WORKSHEET!$A$1:$S$1000,19,FALSE),"")
    Looking for opportunities

  6. #6
    New Member
    Join Date
    Sep 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a Blank if Error OR 0

    Hi guys, thanks a lot for the replies.

    Will give both options a go and get back to you!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Return a Blank if Error OR 0

    Don't bother trying my suggestion from post#2, as Steve pointed out it won't always work.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Sep 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a Blank if Error OR 0

    Thanks for your help guys.

    Steve the Fish - Your solution works perfectly. You are clearly either a genius or some sort of divine entity.

    Cheers!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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