Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup and/or format woes (2000)

    I've been scratching my head about this one all morning. A friend showed me his spreadsheet where he's using vlookup. It works fine ... sort of. He's just tried to use another vlookup based on the value of the first vlookup. So far so good? The problem is that the second vlookup returns "N/A". Now, if I go back to the first vlookup equation and edit it, the cell displays the formula instead of the value, the second equation then displays the proper value! I can't get the orginal vlookup cell to display the value (it just keeps diplaying the formula).

    I've tried formatting the cells as general and as text, but it still keeps giving me this weird behavior. Any clues/suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup and/or format woes (2000)

    I think we'd have to see the spreadsheet (or a stripped down version of it.)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    The file

    OK, I'm submitting the whole thing as I'm not sure about how my named range will react to truncation. Note that it's mostly in Spanish, but that shouldn't affect it (right?).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: The file

    Column D in the Cuadres worksheet is formatted as text, so if you edit the formula, it will then be interpreted as text.
    The VLOOKUP formulas in column E don't have a 4th argument, so VLOOKUP tries to find the largest value less than or equal to the search value. You probably want an exact match.
    Column A in the SAP worksheet is also formatted as text, so if the first VLOOKUP formula returns a numeric value, the second formula won't find it.
    If you change this, there are still many #N/A results, because there is no match. For what it's worth, I have attached a modified version.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mucho gracias!

    You, sir, are a Prince! Now I'm going to sit down and figure out just what the heck I should have done the first time (that TRUE/FALSE switch is a real mystery)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mucho gracias!

    Hopefully, this example can make the difference clear

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><tr><td align=center>1</td><td align=right>1</td><td>John</td><td align=right>

Posting Permissions

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