Vlookup inconsistencies and remedy

[Tigerexcel]

A 2 part question. Everyone has had the issue of Vlookup returning an error when a field is formatted as text and it requires a number. I run into this issue but it doesn't always return an error, sometimes even on the same files it returns inconsistent results. I notice it particularly when I download an external file. What is the main factor in causing this error, is it that the lookup value is formatted as text, or is the table array the major issue? The lookup formula will work sometimes when one or the other is formatted as General or Number but not always.
Even when you have formatted both the lookup value and the corresponding entry in the table array, a re-set is sometimes required to return the correct value. I usually do this by re-typing some of the lookup formula and that does the trick. I'd like to combine all these steps into a one-step process which will probably a macro so that I speed up the processing, anyone have such a macro?

 

[Tigerexcel]

Thanks to Jason and Mark for resolution of this vexing issue. It does appear that it's not important whether the source files are formatted as text, it's the cell that has the formula that is the key and if it's formatted as text then it will lead to the situation mentioned.
Another interesting quirk is that if you use find and replace with an = sign in the formula then it returns a value/correct entry. If you use F2 and re-type = it doesn't work. With my example I left it as Text, the formula corrects itself with a replace even though it still reads as text format.