VLOOKUP says N/A but the value is right there — hidden spaces cause
VLOOKUP throwing #N/A when you can see the value sitting right there in the table? Hidden spaces or a text-versus-number mismatch are breaking the exact match VLOOKUP requires. Here's how to find the invisible difference
Why "it looks the same" doesn't mean it matches
VLOOKUP compares the lookup value against the table's first column character by character, and a comparison like that has no tolerance for anything beyond the visible digits or letters — an extra space at the end, or the number 500 sitting in one cell as text and 500 in another as a real number, is enough to make an otherwise identical-looking value fail the exact match completely.
How to isolate which side is wrong
Put =TRIM(A2)=TRIM(B2) in a spare cell comparing the lookup value against the table value — if that returns TRUE, whitespace is the problem, since trimming made them equal. If it still returns FALSE, check =ISNUMBER(A2) against =ISNUMBER(B2); one coming back TRUE and the other FALSE confirms a text-versus-number mismatch instead. Checking =LEN(A2) against the number of characters you'd expect to see can also flag an invisible extra character even when TRIM alone doesn't fix it, which usually points to a non-breaking space rather than a normal one.
Fixing it for good
For whitespace, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) on both sides, since that clears regular spaces and non-breaking spaces in one pass. For a type mismatch, convert the text side to a real number with =VALUE(A2), or convert the number side to text with =TEXT(A2,"0") if text is what the table actually needs — pick one side and make the two consistent rather than patching the lookup formula itself.