+ 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,142

    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

  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