Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting? Causing Problems with Vlookup (2000)

    Hi, I am using a Vlookup to try and determine matches. Apparently the way the numbers are formatted is causing this not to work. I have tried to clear the formatting then formatting both sets of numbers as either numerical or text data to no avail as well as removing all spaces. I am attaching a list of the two sets the numbers ( one contains many more numbers than the other). Any ideas on how to fix this such that I don't have this problem and a match is indeed a match????? For example, each column has 1005454 in it but don't show up as a match using vlookup.

    Thanks in advance.

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

    Re: Formatting? Causing Problems with Vlookup (2000)

    You don't explain how (and why) you use VLOOKUP. I have no problem matching the values in the workbook you attached. What are you trying to accomplish?

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

    Re: Formatting? Causing Problems with Vlookup (2000)

    I had no problem looking up any of the values in Column A in Column B using VLOOKUP and returning a value that I put in column C. Could you include an example of the problem you are having?
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting? Causing Problems with Vlookup (2000)

    Reply to Hans and Legare, I have data that may have passed into one system in one month; however, does not pass into the other system until another month. What I do in my monthly reconciliation is identify the "timing differences." Consequently, I use a Vlookup to look at the invoice numbers (what I attached on the spreadsheet) to look between the 2 sets of data. When a match occurs, I like to return some verbiage, such as "Previous Month." So, I used this Vlookup process with a "false" in the last field looking for exact matches. There should have been 39 matches if memory serves me correctly; however, there were only 2. I try to clear the formatting, then format both as numerical, text, etc. and seems like it is impossible to get these things to "match" using the Vlookup criteria.

    In short, how can a determine how a cell is formatted and how can I, with assurance, determine that 2 cells have identical formatting?

    Thanks for the help!

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

    Re: Formatting? Causing Problems with Vlookup (2000)

    See attached zipped workbook; it uses MATCH instead of VLOOKUP, since you only want to know if there is a match, you don't need to look up something in another column.

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

    Re: Formatting? Causing Problems with Vlookup (2000)

    To determine if the value in a cell is text or number, you can use a formula like this:

    <pre>=IF(ISTEXT(A1),"Text","Number")
    </pre>


    However, you also need to know that just changing the format of a cell that already contains a value from Text to Numeric or from Numeric to Text will NOT convert the value in the cell from one to the other. You have to change the format and then do something to change the format of what is there. Exactly what you do depends on what you are expecting to happen.
    Legare Coleman

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting? Causing Problems with Vlookup (2000)

    What formatting are you interested in:
    Number format, bolding, underlining, italics, color, etc?

    All would require VB to determine. A possible snag is that individual characters have formatting also so the cell may be "neither" (bold, Italics, underline) or have "multiple colors". Do yu need to compare all formatting?

    Steve

Posting Permissions

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