Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005

    Unanswered: Inconsistent results from VLOOKUP??

    I have used Excel for some years, but now I am in front of something I can't manage,
    I have a plain database, and I look up there some info with the VLOOKUP. I get for some references the desired info, but for some others I reveive #N/A.
    Obviously, I confirmed that the info is there indeed, and in the same format, and copied data between lines, and still I get the damned #N/A.
    I couldnt find anything different between a line that works and one that not works.
    I isolated the non working formulas and I can't find nothing wrong.
    I am even more frustrated because among my friends and workmates I am the "Excel guy", and I this problem is becoming a sort of personal challenge.
    If you are willing to give me a hand I can send you my XLS file, which has no macros and no references to other files. Anyway it is here available in a ZIP file.

    I really thank you very much
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Aberdeen, Scotland
    Not a very good answer here but if you test out a few of your values you will notice that the vlookup works with some and not with others

    if you change the range lookup to true then the answers come back without a problem

    this leads me to believe that excel isnt storing your numbers exactly but probabbly storing them like this 134.0000000000000000001 is equivalent to 134 etc,

    But this is just a guesss

  3. #3
    Join Date
    Oct 2003
    I agree with Dave. I played with the data and formulas for a while, and found the same thing.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Mar 2004
    Minnesota, USA, Earth


    Since the data in the first column of the array is a calculation, I would suggest doing something like...


    I tested the TRUNC and it seemed to solve the problem with the #N/A errors. These both do the same thing, but use which ever you are more comfortable with using.

    If you are not sure that the data is in the array (the normal cause for #N/A errors), then try a formula like...
    =If(ISERROR(VLOOKUP([your data here])),0,VLOOKUP([your data here])).

    It doubles the number of vlookup calculations, but gets filters out the #N/A errors.

    Good Luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts