Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Cell Format & Vlookup Problem (Excel 2002)

    Hi,
    I've had this problem about a million times and I'm tired of fixing it by multiplying it by one... I'm hoping someone has a better solution. Here is my problem:
    I use the vlookup function all the time... when I'm using the vlookup function to look up an account number (let's call it account number 8000140), sometimes the vlookup recognizes it and finds a match, and sometimes it doesn't. I'm assuming since sometimes I can multiply it by 1 it reverts it to a number then the vlookup up finds a match. For the life of me, I can't find out why sometimes it works and sometimes it doesn't. I make sure the format of the cells in the vlookup data are the same as the data I'm looking up, and there is still a problem. Sometimes I have to multiply BOTH the vlookup data and the data being looked up by one and then it works. I also have this problem with things that are alpha-numeric as well (for example 75CCORP)... I multiply it by one and the vlookup finds a match. This is the solution that about 15 people in our finance dept currently use... there's got to be a better way. HELP PLEASE... my patience has finally worn thin on this one... I appreciate any help that someone can throw my way!
    THANKS!
    LJM

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

    Re: Cell Format & Vlookup Problem (Excel 2002)

    It sounds like you are mixing numeric and text values, but it is impossible to say for sure without seeing a workbook with the problem. Could you upload workbook that demonstrates the problem? It is also possible that you are not setting the fourth parameter to VLOOKUP correctly. If you are looking for exact matches, the fourth parameter should be set to FALSE.
    Legare Coleman

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

    Re: Cell Format & Vlookup Problem (Excel 2002)

    The Account # on the JEUpload worksheet is text and the Unit Descr on the vlookup sheet is numeric. You are not going to get a match between those two. The cells on both worksheet have been formatted as TEXT, however that does not change what is in the cells. If you enter a numeric value into a cell and then change the cells format to text, the value in the cell remains numeric. If you format a cell to TEXT and enter a number into it, it gets entered as a text string. Changing the cell format to a number format will not change the text number into a numeric value. Since I don't know how the worksheet was created, I do not know how you got what you have. To get the VLOOKUP to work, you are either going to have to change the numeric values to text strings or change the text strings to numeric values.
    Legare Coleman

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Cell Format & Vlookup Problem (Excel 2002)

    Hi Lana,

    The problem may have something to do with how you're importing the data, since the Account# on the JEUpload sheet is text while on the vlookup data sheet is numeric (though both are formatted as text and right-aligned).

    Anyway, the following TrimRange macro (which calls the two above it), which is designed to trim unwanted spaces from strings, seems to fix the problem when I run it against both sheets:

    <pre>Private Sub MacroEntry()
    'Store Status Bar Visibility
    SBar = Application.DisplayStatusBar
    'Force Status Bar Visibility
    Application.DisplayStatusBar = True
    ' Suspend ScreenUpdating
    Application.ScreenUpdating = False
    ' Stop Re-Calcs
    Application.Calculation = xlManual
    End Sub

    Private Sub MacroExit()
    ' Restore Re-Calcs
    Application.Calculation = xlAutomatic
    ' Remove Message From Status Bar
    Application.StatusBar = False
    ' Restore Status Bar Visibility
    Application.DisplayStatusBar = SBar
    ' Restore ScreenUpdating
    Application.ScreenUpdating = True
    End Sub

    Sub TrimRange()
    Call MacroEntry
    On Error Resume Next
    Dim Cell As Range
    Dim CellCount As Long
    Dim Percent As Integer
    Dim I As Long
    I = 0
    If Selection.Rows.Count * Selection.Columns.Count > 1 Then
    CellCount = Selection.SpecialCells(xlConstants).Count
    Else
    CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
    End If
    For Each Cell In Selection.SpecialCells(xlConstants)
    Cell.Replace What:=Chr(160), Replacement:=Chr(32)
    Cell.Value = Application.Trim(Cell.Value)
    I = I + 1
    If Int(I / CellCount * 100 + 0.5) = Percent + 1 Then
    Percent = Percent + 1
    Application.StatusBar = Percent & "% Trimmed"
    End If
    Next Cell
    MsgBox "Finished trimming " & CellCount & " cells.", 64
    Call MacroExit
    End Sub</pre>


    You might want to add the macros to your Personal.xls workbook, so that they're always available, and assign TrimRange to a keyboard shortcut. Alternatively, just add them to your acounting workbook.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Cell Format & Vlookup Problem (Excel 2002)

    My vlookup formulas always have "false" as the fourth parameter. The problem most definitely is a numeric and/text mix issue.... however it's odd that the account number (all numeric = 8000140) doesn't show a match and of course I know it matchs as it's in the vlookup data reference. When I multiply one or both by the 1, then it "resets" itself and recognizes a match. I've attached my current spreadsheet which presented itself with this problem... keep in mind that some of the data is downloaded from the AS/400, so maybe it's formatting characteristics are causing the problem... I don't know... sometimes if I simply re-key the 8000140 by hand, then it matches. It's got to be a formatting thing. Of course re-keying anything kind of defeats the purpose, as you know... and as mentioned earlier, I don't always get this problem... I download and use vlookups EVERY day... 95% of the time I don't have this problem... then it rears it ugly head.
    Thanks,
    Lana

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Cell Format & Vlookup Problem (Excel 2002)

    Thank you Legare!
    LJM

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Cell Format & Vlookup Problem (Excel 2002)

    Thank you macropod!
    I will try this out next time it occurs... it's a pesky little problem that doesn't ALWAYS occur, when it does it's quite irritating.
    Thanks again!
    Lana

Posting Permissions

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