Announcement

Collapse
No announcement yet.

Replace VLOOKUP #N/A error with blank or zero

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Replace VLOOKUP #N/A error with blank or zero

    While using vllookup am having trouble with the result when value is not recognized or even present. I would like the result in the cell to be blank if the value sought is not found. Am now getting #N/A or just #. As I'm trying to sum the results am failing miserably!! Any help please

    WilliamO
    Last edited by AAE; July 31st, 2010, 00:31. Reason: revise thread title

  • #2
    Re: Replace VLOOKUP #N/A error with blank or zero

    Ivanhoe,

    Welcome to the Ozgrid community.

    Because thread titles are important for obtaining good search results they should be written with this thought in mind - that is, the thread title should be "search friendly", meaning a searchusing YOUR title as the search terms would yield relevant results. Thread titles should concisely nd accurately describe the contents of your thread. A single word title like "vlookup" is inadequate as it is too generic and does not fully describe your thread.

    Note the change made to your thread title as an example and in future threads take care to give the thread a title that more acurrately reflects your thread content. Thanks.

    There a MANY posts on this topic and more than one way to deal with this issue;
    See these links on the VLOOKUP #N/A Error
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      Re: Replace VLOOKUP #N/A error with blank or zero

      Many thanks for the post and the advice re describing the problem being experienced. Will make sure I'll do it better next time. Got the answer I needed and it works well with my project. BTW do you have any advice re VBA for Excel as I've done zero work with this sort of thing and as am retired and doing just odd jobs with spreadsheets for friends and businesses in the area am wondering how useful it could be. And also am wondering how attainable a working knowledge in VBA could be learned this way. Any thoughts? Ivanhoe (William)

      Comment


      • #4
        Re: Replace VLOOKUP #N/A error with blank or zero

        If you're at all technically inclined, or just get a kick out of figuring out how to simplify a complex or tedious/repetitious task, then I believe it would most definitely be worth your while to acquire some familiarity with VBA.

        Particularly in Excel, it is a readily attainable skill, and you can start small and build from there. The macro recorder will capture the command syntax required for most actions you can carry out using the toolbars (or ribbons in 2007 and later). There are some excellent reference books available to help - for the complete novice, John Walkenbach's VBA Programming for Dummies series is quite good (with editions for each version of Excel).

        Most important advice for starting out: while the macro recorder will show you commands using the "Select" method, when you go to write your own code, it is nearly always unnecessary. You can reference cells, sheets, workbooks, rows, columns etc. without having to select them. Another extremely useful trick to master early is assigning excel objects to variables, allowing you to operate on them without having to key long detailed names for them over and over.

        Good luck! And know that you'll find lots of willing helpers in forums like this one.

        Comment


        • #5
          Re: Replace VLOOKUP #N/A error with blank or zero

          For VBA training, this might be of interest if you want a more "formal" approach than just digging through books.

          Ozgrid VBA Training

          Additionally, do a web search on the topic as well as make use YouTube. There are some decent, free, beginner tutorials around.
          As Barb stated, the macro recorder is your friend and you can learn much, although the code it spits outs isn't the mode efficient, but that's where the forum comes - getting experienced help to review and help tweak it.

          Download and study the code/workbooks/projects of others then post specific questions.
          AAE
          ----------------------------------------------------

          Forum Rules | Message to Cross Posters | How to use Tags

          Comment

          Working...
          X