Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: VLOOKUP Results - removing 0

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOKUP Results - removing 0

    Alright, I have done a search and did not find for 0, for #N/A yes, but not 0.

    Here is the formula, very straight forward: =IFERROR(VLOOKUP(A:A,Physician!A:P,5,FALSE),"")

    In the results I am getting 0 instead of a "blank".

    Any suggestions?

    -T

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,673
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    Hi, what does your formula return when there is a valid lookup - is it always a number, always text or can it be a combination of both?
    [code]your code[/code]

  3. #3
    New Member
    Join Date
    Oct 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    Quote Originally Posted by FormR View Post
    Hi, what does your formula return when there is a valid lookup - is it always a number, always text or can it be a combination of both?
    It can be a combination of number, text or both.

    I have used this in the past with no issues, so I thought it was a formatting thing, but that is not the case, so I am kinda stuck, I need the field to be blank if there is no value, and it returns 0. Can't have 0 in a report, lol

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    The problem is that 0 is NOT an error, it's a perfectly valid numeric value.
    Therefor IFERROR does not return "".

    Now there are 2 possible reasons for VLOOKUP to return 0.

    1) The cell corresponding to where the matching value was found is Actually a 0 (0 is a real numeric value)
    2) The cell corresponding to where the matching value was found is Blank/Empty

    How to resolve depends on which (or both) scenario is the cause of your 0 from Vlookup.
    Last edited by Jonmo1; Nov 7th, 2017 at 11:50 AM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,673
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    You can suppress the zero from displaying by using a custom format along the lines of.

    General;-General;

    Or you can remove it totally with something like this:

    =IFERROR(IF(VLOOKUP(A1,Physician!A:P,5,FALSE)=0,"",VLOOKUP(A1,Physician!A:P,5,FALSE)),"")

    Note that the lookup_value should refer to a single cell - not the whole column; change this to the cell that contains your look up value.
    [code]your code[/code]

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,417
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    There is an issue with your VLOOKUP formula.
    The first argument (the value you are looking up/matching on), should be a single cell, NOT a whole column!
    Code:
    =IFERROR(VLOOKUP(A:A,Physician!A:P,5,FALSE),"")
    So, it should look something like:
    Code:
    =IFERROR(VLOOKUP(A1,Physician!A:P,5,FALSE),"")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Oct 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    Quote Originally Posted by Jonmo1 View Post
    The problem is that 0 is NOT an error, it's a perfectly valid numeric value.
    Therefor IFERROR does not return "".

    Now there are 2 possible reasons for VLOOKUP to return 0.

    1) The cell corresponding to where the matching value was found is Actually a 0 (0 is a real numeric value)
    2) The cell corresponding to where the matching value was found is Blank/Empty

    How to resolve depends on which (or both) scenario is the cause of your 0 from Vlookup.
    Understood, and the value that is in the cell is Blank/Empty, guess that is why I didn't understand the 0, I assumed it would stay Blank/Empty with the IFERROR(.................),"")

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    The basic issue is that a formula cannot return 'Nothing' or 'Blank'.
    All formulas MUST return 'Something'
    So if the result of a formula is a reference to an empty cell, it returns it as a 0.

    So my question here is do you need to differentiate between a cell with an actual 0, and a blank/empty cell ?
    with this formula
    VLOOKUP(A:A,Physician!A:P,5,FALSE)
    In column E, the resulting value of the vlookup.
    Is a 0 an actual real possible result?
    Last edited by Jonmo1; Nov 7th, 2017 at 12:07 PM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    New Member
    Join Date
    Oct 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    Quote Originally Posted by Jonmo1 View Post
    The basic issue is that a formula cannot return 'Nothing' or 'Blank'.
    All formulas MUST return 'Something'
    So if the result of a formula is a reference to an empty cell, it returns it as a 0.

    So my question here is do you need to differentiate between a cell with an actual 0, and a blank/empty cell ?
    with this formula
    VLOOKUP(A:A,Physician!A:P,5,FALSE)
    In column E, the resulting value of the vlookup.
    Is a 0 an actual real possible result?
    It is not a 0 it is an actual result (i.e.) Mr. Brown, however, if there is no value it is blank.

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,417
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VLOOKUP Results - removing 0

    It is not a 0 it is an actual result (i.e.) Mr. Brown, however, if there is no value it is blank.
    Based on how you wrote your formula, it might not actually be looking up the value you think it is.
    You should really adjust your formula like FormR and I described, so you tell it exactly which cell you are looking up (instead of using a complete column reference in your first argument).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

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
  •