Forum Discussion
Solved
vlookups and formatting issues
Hi! I am working on vlookup, and I cannot seem to figure out the formatting for the table array file. I am getting #N/A all down the column. I know my formula is correct bc when I type in the lookup value myself in the table array, the value shows up on the other sheet. The lookup values here are names. Both files had the columns formatted as "general." Knowing "text to column" solves formatting issues with numbers, I formatted both sheets to have the column be "text" & did "text to column" to no avail. I have also tried to copy the table array sheet & paste special values into a new spreadsheet. This did not work, either. What am I missing? Your help is greatly appreciated!!
I see what your problem is. It this
Can you see it there?
No.
And that illustrates the problem. And it's not in your formula.
Let me be more descriptive: There are SPACES after the names in the "Table array" file, the source file. And VLOOKUP is looking for an exact match using names that DON'T have spaces following them.
Now there are a number of ways to solve it. Perhaps the best would be to head it off at the source, if you can do that. Does that source data come from somebody else? Can you ask them to deliver "clean data"?
A second possibility, if you have the ability to modify that "table array" file yourself: the function TRIM will remove leading and trailing spaces from the column.
Let's start with those two possible fixes, with the first being the best by far....get clean data to start with.
The learning here vis-a-vis VLOOKUP and similar functions is that they can often be messed up by "invisible" or nearly invisible distinctions. The computer sees those names as not matching because of the trailing spaces; that's a common cause of errors and easily overlooked because our eyes and brains can still see them as "right answers" and even overlook small things such as minor misspellings. To the computer, though, "exact match" means EXACT MATCH.
hi! my vlookup is missing to exel,how ca I find it? thank you
Is it possible for you to post a copy of (or an extract from) the actual spreadsheet? Without that, despite your quite thorough description, the best one could do is speculate wildly on what's going on. And I doubt that would be very helpful.
mathetesHi! Yes, a very valid point! Ok, so here is a sample of the 2 sheets with which I am working. In the "origsheet," I left my formula, so you can see where the value was returned, once I typed the names in myself. For the "table array" sheet, I needed to remove some columns, but I am trying to return the values from Column D. Thanks in advance for your assistance!
I see what your problem is. It this
Can you see it there?
No.
And that illustrates the problem. And it's not in your formula.
Let me be more descriptive: There are SPACES after the names in the "Table array" file, the source file. And VLOOKUP is looking for an exact match using names that DON'T have spaces following them.
Now there are a number of ways to solve it. Perhaps the best would be to head it off at the source, if you can do that. Does that source data come from somebody else? Can you ask them to deliver "clean data"?
A second possibility, if you have the ability to modify that "table array" file yourself: the function TRIM will remove leading and trailing spaces from the column.
Let's start with those two possible fixes, with the first being the best by far....get clean data to start with.
The learning here vis-a-vis VLOOKUP and similar functions is that they can often be messed up by "invisible" or nearly invisible distinctions. The computer sees those names as not matching because of the trailing spaces; that's a common cause of errors and easily overlooked because our eyes and brains can still see them as "right answers" and even overlook small things such as minor misspellings. To the computer, though, "exact match" means EXACT MATCH.
