1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

(Solved) Vlookup In Excel

Discussion in 'Business Applications' started by PALUPA, Oct 9, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. PALUPA

    PALUPA Thread Starter

    Joined:
    Oct 9, 2003
    Messages:
    3
    Does anyone know why vlookup in excel would fail to recognize an alpha-numeric cell when the match definitely exists? This occurs when I have created a spreadsheet from data transferred from SAP. The data cells are both strictly numeric and alpha-numeric. Vlookup works fine (finding an exact match on numeric fields) but returns #N/A on the alpha-numeric. I cannot use "TRUE" in the range lookup as I need exact matches and I know the exact match is in the array I'm looking in. I've tried clearing all formats, formatting them as text, etc. but it doesn't work. IT DOES WORK when I create a test grid exclusively in excel........ any ideas??
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Vlookup works fine (finding an exact match on
    >> numeric fields) but returns #N/A on the alpha-numeric

    Are ya *sure* this is the whole thing? If you have to steer clear of VLOOKUP's 4th arg, try sorting the lookup data (by lookup column ascending).

    If still stuck, I could have a look at a sample of the data transferred from SAP if you like.

    HTH,
    Andy
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  4. PALUPA

    PALUPA Thread Starter

    Joined:
    Oct 9, 2003
    Messages:
    3
    Yep! The lookup columns are sorted in ascending order and I even tried doing a "paste special" into another worksheet to eliminate any wierd formatting brought in from SAP. 99% of the data will bring in the correct data if "TRUE" is used as the fourth argument but I need it to match exactly.
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Palupa:

    FALSE means FIND ME THE EXACT MATCH ONLY
    If there is no match, it should return #N/A

    If you're not getting returns on ones that you feel you should be, please highlight the ones not being found and let me take a look at your file. Strictly confidential. [email protected]
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Paul's data needed to be trimmed. I had my favorite coder with me on Yahoo, so I asked, and he gave me code to trim the "current selection".

    To use it, open the file, hit Alt+F11.
    Choose your file on the left-hand side.
    Hit Insert-Module.
    Paste the following code into it:

    Code:
    Sub TrimSelection()
    Dim cll As Range
       For Each cll In Selection.Cells
         cll.Value = Trim(cll.Value)
       Next cll
    End Sub
    Hit the diskette icon to save the file.
    Close the VB editor window.
    Select the cells you need to trim.
    Tools-Macro-Macros and choose TrimSelection

    Of course, we provided the formula too:

    =Trim(a1)


    (Are you impressed, XLGuru? Hee hee.)
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Paul: When you get a chance, let everyone know it worked, and we'll mark this question solved. :)
     
  8. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> (Are you impressed, XLGuru? Hee hee.)

    Now, "who uses code when you don't have to?" is something I *can* subscribe to.

    Select the "data from SAP" column. CTRL+H. Find What = " ", Replace All.

    I can't believe you have a favorite (dern) coder.

    ;) ,
    Andy
     
  9. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    My...yes. I bow to your expertise. :D

    Of course I have a favorite coder. He does EVERYTHING.
     
  10. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    I'm a casual user of Excel and have found ASAP Utilities to be very helpful especially for doing things like deleting leading and trailing spaces and not having to insert another column.

    www.asap-utilities.com
     
  11. PALUPA

    PALUPA Thread Starter

    Joined:
    Oct 9, 2003
    Messages:
    3
    So far ...so good. I need to manipulate a lot of SAP data in excel so knowing the code to use for the "garbage" that is brought in from SAP will definitely save me a lot of frustration.

    Thanks to all of you for your help...... "this" case closed.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - (Solved) Vlookup Excel
  1. jassy6939
    Replies:
    3
    Views:
    126
  2. ranger1
    Replies:
    5
    Views:
    250
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/170733

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice