Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Christchurch, New Zealand
    Posts
    250
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Vertical Lookups (SR1, NT)

    I am trying to use a vlookup to extract information from one file into another. The vlookup works perfectly and does everything I want it to do when I have put together sample files.

    However, the file I want to use it in has thousands of rows. The lookup SEEMS to be working, as long as I don't put in a pattern match. When I use the range_lookup argument it doesn't work.

    I'm trying to use:
    =vlookup(A2,'Filename.xls!CodeTable',2,FALSE)

    Does anyone know if there is a row limit with vlookup?

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vertical Lookups (SR1, NT)

    In what way doesn't it work?
    Are the data sorted properly?

    ruth

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Christchurch, New Zealand
    Posts
    250
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Vertical Lookups (SR1, NT)

    Hi Ruth,
    The first column in the table contains alphanumeric codes and we have sorted this in ascending order.

    When we use a lookup without the range_lookup argument it appears to work as if the range_lookup was set to FALSE. That is, it finds the exact match. When we use the range_lookup and set it to FALSE (expecting to get exact matches) we get #N/A even when we know there is a match.

    Confused? I certainly am!

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vertical Lookups (SR1, NT)

    When you omit the range_lookup argument, it treats it as if you had entered TRUE and vlookup therefore finds the closest match. When you enter False, it searches for an exact match and that, as I understand it, is your problem. The exact match comes up empty.

    What is the source of the data either in your lookup table or in your spreadsheet you are getting the lookup value from? If any of it was imported, try taking it times 1 with a paste special multiply. We've had it where we've even had to do this with text on imported data before...

    We've had some pretty lengthy lookup tables before, and I'm not aware of any kind of limit on their length. How many thousands are you talking about?

Posting Permissions

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