+ Reply to Thread
Results 1 to 8 of 8

#N/A in VLOOKUP Function - How to get rid of them

  1. #1
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Perth
    MS-Off Ver
    Excel 2010
    Posts
    208

    #N/A in VLOOKUP Function - How to get rid of them

    Hi guys,

    Please find attached the sample excel file I have been working on so far.

    Can anyone help me to get rid of the #N/A from the cells that does not match the criteria?

    I've tried ISNA already and it didn't seem working properly on my excel function.

    Any help will be greatly appreciated.

    Plus, any idea why column K generate '0' value?

    1. To get rid of #N/A

    2. And also to get rid of '0' from column K

    Cheers,
    Attached Files Attached Files

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: #N/A in VLOOKUP Function - How to get rid of them

    In query #1 - you can check for cell K first

    =if(K17="","",your existing formula here... )

    your second query

    i think there will be effect co'z your match is using 0 but you can do it like this

    =iferror(if(your index/match formula)=0,"",your index/match formula),"")
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Perth
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: #N/A in VLOOKUP Function - How to get rid of them

    Hi Vlady,

    Please forgive my ignorance.

    Would you be able to show me how to apply the 'If fuction' you have suggested above to my excel formula using the 'sample excel file' that I have attached above?

    That way, I would be able to have a clearer understanding of your suggestion.

    Cheers,

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: #N/A in VLOOKUP Function - How to get rid of them

    in cell O17

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    in cell K17 is a problem regarding displaying blank for zero because 0 is being utilize in the match, but this is a formula ( i think not applicable) just for the sake of controling the 0 to become blank

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the bold parts logically test the outcome of the index/match if it is zero then display blank/null ""

  5. #5
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Perth
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: #N/A in VLOOKUP Function - How to get rid of them

    Hi Vlady. By using the formula you suggested, I could finally get rid of #N/A from my excel spreadsheet. However, Problem 2 still remain unfixed. Seems like it is not fixable usnig 'fuctions'. But I really appreciate your help fixing the problem 1. Thans M8. Merry Christmas.

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: #N/A in VLOOKUP Function - How to get rid of them

    yes, you need all values including the 0 for the formula... but if yu don't want to see it. you can uncheck the excel options in
    Option ->Advanced -> scroll down search for the "Show a zero in cell tha have zero values" untick the box. It will hide zero values...

    Regards
    vladimir

  7. #7
    Registered User
    Join Date
    09-24-2007
    Posts
    6

    Re: #N/A in VLOOKUP Function - How to get rid of them

    try this.
    K17
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    L17
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Perth
    MS-Off Ver
    Excel 2010
    Posts
    208

    Re: #N/A in VLOOKUP Function - How to get rid of them

    GOSH.....smart89. Your formula worked. Unbelievable. Thanks m8.

+ 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