+ Reply to Thread
Results 1 to 8 of 8

Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

  1. #1
    Registered User
    Join Date
    03-24-2016
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    41

    Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

    Hi,

    I have a weird situation where things display inconsistently in my workbook - the same code does what I want in 1 sheet but not in another.

    I'm using this code to look up a patient name from the "Demographics and Contact Info" sheet based on the ID number in column A of that sheet, to autopopulate the name into the "Treatment Info" sheet when the ID number is entered in column A of that sheet:

    =IF(ISNA(VLOOKUP('Treatment Info'!A6,'Demographics and Contact Info'!$A$5:$B$686,2,FALSE)),"",VLOOKUP('Treatment Info'!A6,'Demographics and Contact Info'!$A$5:$B$686,2,0))

    This formula results in a cell that reads "0" if someone enters an ID # in "Treatment Info" that did not have names entered in the "Demographics and Contact Info" sheet. I want it to display a blank cell and not a 0. HOWEVER, I use an identical formula (only with sheet name changed) in a third sheet called "Navigation Record" that correctly just displays blank cells instead of 0 in this same scenario of finding a null result:

    =IF(ISNA(VLOOKUP('Navigation Record'!A6,'Demographics and Contact Info'!$A$5:$B$686,2,FALSE)),"",VLOOKUP('Navigation Record'!A6,'Demographics and Contact Info'!$A$5:$B$686,2,0))

    What can I do to fix the display for "Treatment Info" to blank? Why is there a discrepancy in how the same formula is working? In both sheets, the set of cells are "General" format. I even tried format painting from Navigation Record (with the desired output display) to Treatment Info to fix it but it doesn't work.

    Thanks in advance!!

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

    Not sure why 1 shows blank, but the other shows 0.
    It's probably a formatting issue where 0's are hidden.

    Can you post a copy of the book showing BOTH formulas (1 that shows 0, 1 that shows blank)

    But, if you're talking about a 0 returned because the resulting cell of the vlookup is blank
    And the normal expected result of the vlookup is a Text String (A Name), try

    =IF(ISNA(VLOOKUP('Treatment Info'!A6,'Demographics and Contact Info'!$A$5:$B$686,2,FALSE)),"",T(VLOOKUP('Treatment Info'!A6,'Demographics and Contact Info'!$A$5:$B$686,2,0)))

  3. #3
    Registered User
    Join Date
    03-24-2016
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    41

    Re: Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

    Jonmo1, your code fixed the issue!!! I was messing with it for hours. Thanks so much!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

    You're welcome

    Also, since you're using XL2010, you can use IFERROR instead of IF(ISNA

    Try
    =IFERROR(T(VLOOKUP('Treatment Info'!A6,'Demographics and Contact Info'!$A$5:$B$686,2,FALSE)),"")

  5. #5
    Registered User
    Join Date
    03-24-2016
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    41

    Re: Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

    Cool, thanks for the advice! I'm trying to make the file as compatible as possible with all versions of Excel - not sure if that code would still work for older versions?

    Sorry to add another question, but is there a similar T() quick fix for dates, for returning a blank cell instead of a null date value of "1/0/1900"? I'm using a date calculation field in pivot table results, and the blank values are getting counted as January which is not accurate.

    Thanks again for all your help!

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

    To stay compatible with older versions you'd have to stick with the IF(ISNA structure from post #2

    Similar funciton to the T() function for dates, not really.
    There is a little trick that only works in XL2007+

    =IFERROR(1/(1/VLOOKUP()),"")

    This actually works for any numerical value to hide both errors and 0 values.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

    Another way for dates is to use a Custom Format of:

    mm/dd/yyyy;;

    The two semicolons at the end of that string cause negative numbers and zeros to be suppressed.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    03-24-2016
    Location
    Washington, DC
    MS-Off Ver
    2010
    Posts
    41

    Re: Want IF/VLOOKUP formula null result to display blank, but it's displaying 0

    Just to close the loop here, I was able to make one set of cells reference dates from another sheet without error messages using this code:
    Please Login or Register  to view this content.
    I'm still not sure why it gave me issues before and then stopped giving me issues. Field type is general. Just FYI in case it's at all helpful to anyone else.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to return a blank result when using SUM(VLOOKUP formula??
    By Shannon561 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-13-2014, 03:49 AM
  2. [SOLVED] Zero value result displaying as blank cell
    By TimB in forum Excel General
    Replies: 1
    Last Post: 11-06-2012, 12:05 PM
  3. Replies: 2
    Last Post: 05-05-2012, 10:57 AM
  4. If / Vlookup display result not formula
    By Chemistification in forum Excel General
    Replies: 12
    Last Post: 12-09-2009, 07:16 AM
  5. Displaying blank cells instead of null values...
    By gray8110 in forum Excel General
    Replies: 2
    Last Post: 09-13-2008, 11:11 PM
  6. Vlookup is displaying the formula in its cell not the result??
    By Craig H in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2006, 06:15 AM
  7. PivotTable (blank) cells won't display NULL!
    By Abdul Malik Said in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2005, 05:06 PM

Tags for this Thread

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