+ Reply to Thread
Results 1 to 3 of 3

Don't Display a Zero when a VLOOKUP results in nothing

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Don't Display a Zero when a VLOOKUP results in nothing

    I'm using this formula to test that two conditions are true and then doing a VLOOKUP to find an email address in a table:

    =IFERROR(IF(AND(MATCH(B3,SU_LAT,0),MATCH(I3,SU_Name,0)),VLOOKUP(I3,'Internal Users'!email_lookup,4,FALSE),""),"")

    However, if both the MATCH criteria are true but there is no entry in the cell that is looked up, the cell containing this formula returns a '0'. How can I avoid it doing so, I need to display '0's elsewhere on the sheet, so I can't turn it off in the Excel Options, I need to do it as part of the formula.

    Can anyone help as I can't figure out how to do this?

    Many thanks

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Don't Display a Zero when a VLOOKUP results in nothing

    hi HangMan, try:
    =IFERROR(IF(AND(MATCH(B3,SU_LAT,0),MATCH(I3,SU_Name,0),VLOOKUP(I3,Book3!email_lookup,4,0)<>0),VLOOKUP(I3,Book3!email_lookup,4,0),""),"")

    a sample Excel file will always help if it doesn't work

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Don't Display a Zero when a VLOOKUP results in nothing

    Hi benishiryo,

    Perfect, that did the trick...

    Many thanks...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1