VLook Up Error

[gymwrecker]

I'm working with a VLOOKUP formula; formula seems to work fine, but when I type in, let's say a certain "name" it returns #N/A, but if I copy and paste the "name" will work. Is if a formatting problem? How can I fix that so when I type in the "name" it returns the match? I tried formatting and still didn't work, not sure why when I copy and paste from my list of options it works... I hope the way I'm phrasing this works...

=INDEX($L$1:$L$90,MATCH(A2,$M$1:$M$90,0))

 

[awoohaw]

Have you looked at the length of cell A2? or done a comparison of the text value of A2? in another cell type =A2="Name" Len(A2) where contents of cell A2 is "Name" should be 4. And I know this may be silly, but is "Name" somewhere in the range M1:M90?

 

[gymwrecker]

awoohaw,

I typed in the LEN function and curiously noticed that some cells returned a total of 7 when they all have only 6 letters; how can I fix that? I noticed that those returning a total of 7 is because they have an extra space. I can just hit the backspace key and fix it, but the problem is that I have 100's of entries that have an extra space; how can I fix them all at once? Six is the correct amount of characters that I need for my VLookUp to work....

Thanks in advance!!!

 

[shinigamilight]

Use

=Left(A1,6)

 

[Fluff]

How about

Sub gymwrecker()
   With Range("M1:M90")
      .Value = Evaluate("trim(" & .Address & ")")
   End With
End Sub