Vlookup data and numeric

  • Thread starter Thread starter Mick
  • Start date Start date
M

Mick

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!
 
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
 
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
 
One of the problems with imported data is the hidden hard spaces at the end
of text.
Let's say B2 hold the name Joe Brown
On 'Pts Earned by Range' locate Joe Brown - let’s say he is in row 12
In F12 type =A12 ='Total Orders'!B2
You should get TRUE
If not, click A12; go to the Formula Bar and hit End. Does this show you
that the entry ends with spaces?
If this is the problem, insert a new column B and use =TRIM(A1) to get the
name without the spaces
use Copy followed by Paste Special >Values to convert these formula to
values. Now you can remove the column A
 
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
 
Back
Top