Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLookup Case Sensative (SR-1)

    What are the rules in using a VLookup for upper/lower case letters?
    I've tested on the Worksheet attached and coming up with different results.

    It's documented in the Help that *Uppercase and lowercase text are equivalent*,

    Also documents is:
    In Microsoft Excel you can use the LOOKUP() function to search for a value within an array of sorted data and return the corresponding value contained in that position within another array. If the lookup value is repeated within the array, it will return the last match encountered. This behavior is true for the VLOOKUP(), HLOOKUP(), and LOOKUP() functions.

    So I would assume it would find the last match each time and I would get a TRUE result for both Upper/Lower case.

    Can someone help? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup Case Sensative (SR-1)

    Have a look at the attached file. vlookup and hlookup return what I would call the first record, whereas lookup does return what I would call the last record. However, you're right about them not being case sensitive

    Brooke
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Jul 2001
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup Case Sensative (SR-1)

    I appreciate your example which confirms the last match, but still doesn't explain what is occuring with the single text letter.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup Case Sensative (SR-1)

    A couple of points.

    1- Your example shows error for all of the lower case letters when the last parameter is False. If upper and lower case characters are equivalent, then this is not an error. Lower case "a" would match Upper case "A", and that should return 43 which it did.

    2- When the last parameter is True, the lookup table must be sorted since the algorithm does a binary search. If the table is not sorted, then the results are unpredictable. SInce XL is using a binary search, if there are two equivalent entires in the table ("A" and "a") then the hit will be on whichever one the binary search gets to first.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: VLookup Case Sensative (SR-1)

    Excel IS behaving as intended.

    Your column F is showing "error" on the upper case letters because with a TRUE parameter on the VLOOKUP the *last* matching value is returned, i.e. the corresponding lower case letter.

    Your column I is showing "error" on the lower case letters because with a FALSE parameter on the VLOOKUP the *first* matching value is returned, i.e. the corresponding upper case letter.

    Hope this helps.

  6. #6
    Lounger
    Join Date
    Jul 2001
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLookup Case Sensative (SR-1)

    Thank you all for your replies. I gave this information to the person questioning the VLookup and pointed him toward using INDEX MATCH in getting correct results. Have a great weekend all! <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

Posting Permissions

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