My guess...

=countif() treats everything as text.

So the text string ="1" (or '1) will be counted as a match as well as the number
1.

But =vlookup() needs to have an exact match.  The string ="1" and the number =1
are not equal in =vlookup()'s eyes.

I'd do my best to make sure that the values in column F are all real numbers or
all real text.

And it's not enough to just change the format of the cells from Text to General
(or from General to Text).

You actually have to change the values.

When I want to convert a text string that's masquerading as a number to a real
number, I'll use this:

Select an empty cell
edit|copy
Select the offending range
Edit|paste special|check add and values

Well, it works for me with very simple data, as follows
     a b c d e f g g h i g h a b c d e f g g h i g h a b c d e f g g h i g
h j k l m n o p q r s t u v w x y z
     1 2 3 4 5 6 7 7 8 9 7 8 1 2 3 4 5 6 7 7 8 9 7 8 1 2 3 4 5 6 7 7 8 9 7
8 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

I've transposed the data so it doesn't go over too many lines, and hopefully
it will wrap gracefully. Do you want to give us a sample of the data you are
using?

By the way, I continued this down over another 450-500 lines, ending up
with:
     aaaaaaaae 213
     aaaaaaaaf 214
     aaaaaaaag 215
     aaaaaaaag 215

So, it seems to work for longer strings and larger than 15-20 numbers.

BTW what version of excel?