Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    VLOOKUP ISSUE (XP & 2003)

    I am trying to perform a VLOOKUP but references on my array have spaces before them. How can I correct this to perform a proper VLOOKUP function. I will attach a sample .

    Thanks

    Martin

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VLOOKUP ISSUE (XP & 2003)

    Try
    <pre>=VLOOKUP(" "&A5,$G$5:$I$109,3,FALSE)</pre>

    or
    <pre>=INDEX($G$5:$I$109,MATCH(" "&A5,$G$5:$G$109,0),3)</pre>

    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP ISSUE (XP & 2003)

    Why not clean up your data in the lookup column by doing one of the following,

    Run ASAP Utilities at

    www.asap-utilities.com

    Run this macro,

    Sub TrimALL()
    'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    'Also Treat CHR 0160, as a space (CHR 032)
    Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    'Trim in Excel removes extra internal spaces, VBA does not
    On Error Resume Next 'in case no text cells in selection
    For Each cell In Intersect(Selection, _
    Selection.SpecialCells(xlConstants, xlTextValues))
    cell.Value = Application.Trim(cell.Value)
    Next cell
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    End Sub

    Or use Find/Replace

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VLOOKUP ISSUE (XP & 2003)

    Thanx for that macro Maxflia. It works well in removing spaces. This is probably the best solution for Martin in the long run. Remove the spaces by selecting the Array (CTRL + SHIFT + *) and running your macro. Once spaces are removed the VLOOKUP works perfectly!

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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