vlookups and formatting issues

MonicaMikol 

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.