Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Function & Data Source
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Function & Data Source
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. "Ed2" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Function & Data Source
Sorry...forgot....Excel 2007
"JLatham" wrote: 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. "Ed2" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Function & Data Source
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,FALS E) "JLatham" wrote: 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. "Ed2" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Function & Data Source
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 "JLatham" wrote: 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. "Ed2" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup Function & Data Source
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,FALS E) 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. "Ed2" wrote: 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 "JLatham" wrote: 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. "Ed2" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup issues when source data is in another workbook? | Excel Discussion (Misc queries) | |||
VLOOKUP - recover missing data source | Excel Worksheet Functions | |||
Vlookup (Source data has a non-visable character) Excel | Excel Discussion (Misc queries) | |||
Why won't vlookup read newly entered data from a source file? | Excel Discussion (Misc queries) | |||
Go to Special or other function to jump to vlookup source? | Excel Worksheet Functions |