There is absolutely nothing wrong with using the absolute addressing so long as it is a set of correct addresses to form the range. There is no sign whatever in your statements that this is not so, so there is no problem whatsoever and it does not cause your formula's failure.
The first answer is correct, of course, about why you get the error you do. Reference errors mean there is a problem with your references. Nothing else. And the source of the difficulty is exactly as stated: a mismatch between your lookup range and the column you wish returned. Excel cannot return column two of a one column range.
Once fixed though, you end up with the NA error. That one means there is nothing that matches. (It can mean many things, of that essential nature, of course, but in a lookup context it means there is no match.)
So... NO MATTER WHAT, your input to the lookup does NOT NOT NOT match any of your lookup range's first column. To solve it, you have to get past the hands off approaches for finding the difference/s: no more relying on "Do I get TRUE if I ask if they are the same?" and "Do the types match?" They provided the best help they could to you, but it wasn't enough.
Full disclosure: using the two lines of data you provided, and a correct lookup range, VLOOKUP() is HAPPY to provide me a correct result.
Try it yourself, but with a difference. For your lookup value, choose ANY entry in $A2:$A8999 and PASTE it into B2. That is how you know you are looking up something that has to succeed so long as the formula is written correctly. It worked for me because I did exactly that. I'd've tried the real source data, but that wasn't provided.
So now that you see for yourself it works you can move past the idea the formula is wrong and solve the data mismatching.
Examine the data carefully. Paste a lookup value in a cell somewhere and paste its apparent match in the cell below it. Do they look any different? No, well, that would have been low-hanging fruit anyway. Choose the first and press F2, then Ctrl-Home. Now move the cursor, deliberately — not machine-gunning it, with the right arrow key watching VERY CAREFULLY. Does it ever seem to not move when you do? Maybe flicker slightly at the same time? Then there's something there that takes no space in the display. Lots of data, and certainly ANYTHING scraped from the internet, has characters that help the display engine in the source position it on the screen. Scraping it off the screen carries this with it.
The likelihood would be your lookup range is populated, at least in part, with material like that. "In part" could have resulted in your testing succeeding, by the way, but failing in use as far more examples got used. And a popular way to avoid "hands on" work to populate one's lookup cell would be to copy a bit of the lookup range and paste them one by one in the lookup cell... for 100% success...ful failure: gosh, they'd ALWAYS succeed wouldn't they? Do the first part to put a list in view, but then type in the lookup cell and see if they work. Not so much in many cases, as it turns out the data is polluted.
So, found something like that, the "non-moving move right"? Well, you have to remove the offending characters from the place you find them. Or if cleaning that would be a beast, find a way to make them not matter. Formulaicly, since you can't interact with the lookup... might as well just use PageDown a lot. Or VBA. One might also be looking at a fine set of lookup data, but be getting the lookup value from a dodgey source, like the internet. Whichever half, or both, it comes from, it's got to be dealt with.
I focus on the non-displaying character/s due to the commonality of that problem. There might well be other dificulties, but I'll bet on that one.
IF the data to be cleansed is ALL basically alphabetical, one way to clean it for a use like this is to use MID(A1,SEQUENCE(1,LEN(A1)),1) to examine it one character at a time. Use an IF() test to check the character's UNICODE() value falls into an acceptable range. That can be done several ways including building a lookup table you populate with acceptable characters. (If so, you'd start with all the obvious ones and add more as failures are reported showing the need for their addition. Sucky, but it is much easier when skipping characters in a range: like you need characters 100-103, not 104, 105-106, not 107 or 108, 109-134, not 135-143, etc. But usually, just a simple unicode character is IN a range, or NOT in a range, is enough. Then use UNICHAR() to return it to being a character if it succeeded and "" if it didn't, and TEXTJOIN() to put it all back together.
You can do that for BOTH input and lookup values, actually. Especially easy to do and look at now we have LET().
That avoids ever having to clean data, you know, with caveats about oen-time occurrences and so on. The simple truth underlying ALL of this is that people expect not to have to get dirty hands, so to speak, to just use formulas and it all works. That's not the real world. "But it should just work!" may be a common lament, but we aren't there yet. For that matter, once we are, your job won't involve this anymore anyway so... hands dirty, success in hand.
Last thing: not having a screenshot of the real thing, or real data complete as it came out of the spreadsheet, one can't really go much further. Hence my providing the "how to" for the formula above, not an exact formula.
