The list didn't exist. He basically got caught bragging one time to a Republican Women's Club that he had a list. He knew, like most, that communists existed in the State Department, but he had no idea who, like most people not investigating the matter. So as part of his diatribe to constituents, he goes off scripts slightly and claims he has a list of X number of known Communists in the State department, brandishing a piece of paper.
But this ended up basically going viral, that McCarthy had a list, and people believed him. He wasn't planning on doing anything with this list, this wasn't some great crusade, instead it was like The Big Lie. Once he started lying, he felt the only way out was through, so he tried to find communists, quickly grabbing the Lee report as a starting point, then exaggerating the facts in that.
Why do we know he was a fake? First, the number of people on it kept changing. Second, he was a known liar, having lied and exaggerated about his war exploits. Third, he was shocked that the media picked up on this, and basically acted unprepared at the beginning, while he was scrambling to come up with a believable story, which he compounded with more lies (i.e. the origin of the list wasn't from a state department official, but a 3 year old report by Robert E. Lee, an FBI agent.
But the Lee list wasn't even full of communists! Sure, there were security vulnerabilities in it, and I'm in no way shaming Lee for what he did, as extramarital affairs and drunkeness are definite security vulnerabilities that can be exploited, but they also aren't card carrying communists.
So yeah, I despise McCarthy, his idiocy led to anticommunism being a joke for years.