(Solved) Vlookup In Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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??
 
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
 

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.
 
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]
 
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.)
 
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. :)
 
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
 
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.
 
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
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top