Forum Discussion
vlookups and formatting issues
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.
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.
- Thank you SO MUCH!!! I understand your explanation perfectly! I wasn't able to get "clean" data, but the TRIM function worked! From there, I just needed to manually check for names that were not an exact match (if we have the name as "Smith" but table array has "Smith, Jr.") I am definitely writing this down, so I have the information readily available should I come across this issue again. Thank you again so much for your time & your detailed explanation!!!