Sometimes when you import data (particularly from an HTML source) you
will get non-breaking space characters which have an ASCI code of 160.
These are different than the normal space characters (with a code of
32). You can get rid of them using Find & Replace as follows:

Select/highlight the data.
CTRL-H (Find & Replace)
Find What: Alt-0160
Replace With: leave blank
Click Replace All

Where Alt-0160 means to hold down the Alt key while typing 0160 on the
numeric keypad.

Hope this helps.

Pete

On Apr 6, 2:05*pm, SMH wrote:
Hi Pete,

I am having the same issue as Mick and it is SO frustrating! *I tried
deleting any spaces from my data, but I still got "N/A" from my vlookup
formula.

I have two wooksheets (Total orders and Pts Earned by Range) and I'm trying
to find the same unique identifier between the two sheets. *I am thinking it
has to be some kind of formatting issue. *Does vlookup require the fields to
be numeric or text? *Also, point of reference, the data I am comparing all
came in from an imported text file (which I think may be the problem).

Here is what I have: =VLOOKUP(B2, 'Pts Earned by Range'!$A$2:$D$270486, 2,
FALSE).

Any ideas? *I could really use some help.

Thank you!

~Beyond frustrated with Excel



"Pete_UK" wrote:
Perhaps you have spaces in the xls file from which you are copying and
pasting into the availability sheet - you can remove these using the
TRIM function, or by using Find & Replace directly after pasting
(replacing space with nothing).

Hope this helps.

Pete

On Jul 23, 12:26 am, Mick wrote:
OK- What I have is an xls file with 2 worksheets (200 and availability) .

The formula I'm using is:

=vlookup($b3:$b202,availability!$A2:$B437,2,False)

'200' B3-B202 a 3 letter code
'Availability' A2-b437 is data(in A) and numeric(in B)

What I want the look up to do is return the numeric value from the
availability sheet to the corrosponding 3 letter code in the 200 worksheet

The data in 200 is static, however the data in availability is copied and
pasted
from another xls file in to the availability worksheet. If the data(ie the 3
letter code) is typed in to availability the formula works, otherwise it
comes up with #N/A.

Is there a formula that needs to be used to format the data?

Thanks in advance for your help!- Hide quoted text -

- Show quoted text -