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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Confused" <Confused@discussions.microsoft.com> wrote in message
news:56354E8B-2298-40C6-B89B-3C747AB42EDC@microsoft.com...
> 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?

[Michiko]

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

"Confused" wrote:

> 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?

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Max" <demechanik@yahoo.com> wrote in message
news:uZR87eB5FHA.2192@TK2MSFTNGP14.phx.gbl...
> 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
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Confused" <Confused@discussions.microsoft.com> wrote in message
> news:56354E8B-2298-40C6-B89B-3C747AB42EDC@microsoft.com...
>> 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]

I've got a 50% chance that the interp was on,
you've covered the other 50%, Arvi ! <g>
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--