VLookup Function & Data Source

  • Thread starter Thread starter Ed2
  • Start date Start date
E

Ed2

I have a 3 page spreadsheet which works flawlessly when I paste data copied
from one source but returns #NA with the same data from another source. I
have checked properties, number vs. test formats and such but to no avail.
AQny suggestions?
 
What version of Excel?

And more importantly, what formula is returning #NA ? Is it a VLOOKUP() or
HLOOKUP()?

Do you get #NA for ALL formulas involved, or just some?

Examples of the formula and data involved would be good to have here.

I suspect that the data from the "other source" that's giving you the #NA is
possibly text, and there may be invisible characters (spaces or tabs most
likely culprits) as part of that data. Added spaces would probably be
noticeable if they're at the start of the data, but not if they're at the end
of it.
 
Sorry...forgot....Excel 2007

JLatham said:
What version of Excel?

And more importantly, what formula is returning #NA ? Is it a VLOOKUP() or
HLOOKUP()?

Do you get #NA for ALL formulas involved, or just some?

Examples of the formula and data involved would be good to have here.

I suspect that the data from the "other source" that's giving you the #NA is
possibly text, and there may be invisible characters (spaces or tabs most
likely culprits) as part of that data. Added spaces would probably be
noticeable if they're at the start of the data, but not if they're at the end
of it.
Click to expand...
 
Thanks for responding J. I thought the same thing about invisible characters
but do not know how to alleviate it. Examples of formulas I'm using are
=VLOOKUP(BB_Users!$A1,BB_Users!$A$1:$L$2000,4,FALSE)
 
31C0D322 128805 The date from each source is formatted as text and all
VLookup lines work fine when I paste from one source but not when the same
data is pasted from another source. I have created a template and am trying
to reproduce the results. Sorry for the separate posts to answer your
question. It has been a long day
 
Here's a formula that will help determine if it's hidden characters that are
messing things up. In a workbook where you ARE getting the #NA results, put
a formula like this in an available column on the sheet with the source data.
If column M on BB_Users sheet is available, put this in M1:
=IF(LEN(A1)<>LEN(TRIM(A1)),"Has Invisible Characters","")
That will tell you if any non-displayable characters are involved.

BUT!! If the formula you provided is truly like what you are using, it
should be IMPOSSIBLE for it not to return a result. You show
=VLOOKUP(BB_Users!$A1,BB_Users!$A$1:$L$2000,4,FALSE)
which says "look up the entry in BB_Users!$A1 in a table made up of from A1
to L2000 and return me the value in the 4th column, list does not have to be
in sequence"
Because the value you're looking up is part of the table itself, it should
be impossible not to find a match.

It might help if you could send me the workbook in question, along with
source data that does work, and source data that doesn't work and I'll look
at it and see what I can figure out here. Send email to (remove spaces)
Help From @ J Latham Site. com
with the files attached. Mention your user name here (Ed2) and if you can
include a link to your first post in this discussion, that will help me
refresh my memory when I get the email, or at least describe the problem and
tell me which set of data works, which doesn't.
 
Back
Top