Vlookup doesn't work until i edit(but not change) the lookup cell

[Confused]

I am using vlookup to lookup values in data copied from a CSV file. It first
returned #N/A so i changed the Number Format (of the Lookup_Value) to "Text"
and it still returned #N/A but when i pressed F2 and then enter (on the
Lookup_Value cell), the vlookup returned the correct value.

This is really driving me up the wall, can anyone help?

 

[Max]

Formatting does not change the underlying values

Try this to convert the text numbers in the lookup col to real numbers

Copy an empty cell, then select and right-click on the lookup col, choose
Paste special > check "Add" > ok

 

[Michiko]

Do, Text to columm to see if there is invisible space in the columm

 

[Arvi Laanemets]

Hi

This method wold work, when OP had changed from Text to Numeric. But by
current setup the only option short of manual editing, is to convert values
to strings in some other column (="" & OldValue), and then overwrite old
values using PasteSpecial>Values

 

[Max]

I've got a 50% chance that the interp was on,
you've covered the other 50%, Arvi ! <g>