How to Activate Vlookup Function?

Not happened only once in current days, and both on my computer and other
users computer.

I have written a most simple Vlookup function like
=VLOOKUP(A2,Sheet2!A:B,2,FALSE), but the result is #N/A. I can find the
matched record in Sheet2, and in tools=>options=>Calcultion, setting is
Automatic. So according to my understanding it should work. And later I found
that If I want to activate my function, I have to double click the A2 cell,
no changes and press Entry.

I have more than 800 records, so to activate it one by one will be a heavy
job.

Do you have any idea about this?

Try instead: =VLOOKUP(A2+0,Sheet2!A:B,2,FALSE)
Copy down

The "+0" is one way to coerce the text lookup number to a real number
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ViestaWu" wrote:
> Not happened only once in current days, and both on my computer and other
> users computer.
>
> I have written a most simple Vlookup function like
> =VLOOKUP(A2,Sheet2!A:B,2,FALSE), but the result is #N/A. I can find the
> matched record in Sheet2, and in tools=>options=>Calcultion, setting is
> Automatic. So according to my understanding it should work. And later I found
> that If I want to activate my function, I have to double click the A2 cell,
> no changes and press Entry.
>
> I have more than 800 records, so to activate it one by one will be a heavy
> job.
>
> Do you have any idea about this?

Hello Max,

I took this try, but failed again. Even after I double click and press
enter, it can't be right. Actually, I set A:A as Text, which are item_no in
my lists and both test and numbers are allowed.

Do you have another idea?

Thanks,
Viesta

"Max" wrote:

> Try instead: =VLOOKUP(A2+0,Sheet2!A:B,2,FALSE)
> Copy down
>
> The "+0" is one way to coerce the text lookup number to a real number
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "ViestaWu" wrote:
> > Not happened only once in current days, and both on my computer and other
> > users computer.
> >
> > I have written a most simple Vlookup function like
> > =VLOOKUP(A2,Sheet2!A:B,2,FALSE), but the result is #N/A. I can find the
> > matched record in Sheet2, and in tools=>options=>Calcultion, setting is
> > Automatic. So according to my understanding it should work. And later I found
> > that If I want to activate my function, I have to double click the A2 cell,
> > no changes and press Entry.
> >
> > I have more than 800 records, so to activate it one by one will be a heavy
> > job.
> >
> > Do you have any idea about this?

The earlier suggestion was my interp from your orig. posts' lines:
> > > that If I want to activate my function, I have to double click the A2 cell,
> > > no changes and press Entry.

Since it appears to be now the other way round <g>, from your lines:
> .. Actually, I set A:A as Text, which are item_no in
> my lists and both test and numbers are allowed.

Perhaps try something like:
=VLOOKUP(A2&"",Sheet2!A:B,2,FALSE)
to convert the real numbers in the lookup col to text numbers
so that these will match what you have in the lookup col of the table_array
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ViestaWu" wrote:
> Hello Max,
>
> I took this try, but failed again. Even after I double click and press
> enter, it can't be right. Actually, I set A:A as Text, which are item_no in
> my lists and both test and numbers are allowed.
>
> Do you have another idea?
>
> Thanks,
> Viesta

> Perhaps try something like:
> =VLOOKUP(A2&"",Sheet2!A:B,2,FALSE)
> to convert the real numbers in the lookup col to text numbers
> so that these will match what you have in the lookup col of the table_array

Another alternative could something like:
=VLOOKUP(TEXT(A2,"0000"),Sheet2!A:B,2,FALSE)
where there are leading zeros in the text numbers [to 4 digits]
in the lookup col of the table_array (eg: 0010, 0100, 0002, etc)

Adapt the "0000" part to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks so much Max.
I tried =vlookup(a2&"", sheet2!A:B,2,FALSE), and it works.
So the reason is, though I set it as text, excel can't recognize it, unless
I alter it to text obligated, right?

"Max" wrote:

> > Perhaps try something like:
> > =VLOOKUP(A2&"",Sheet2!A:B,2,FALSE)
> > to convert the real numbers in the lookup col to text numbers
> > so that these will match what you have in the lookup col of the table_array
>
> Another alternative could something like:
> =VLOOKUP(TEXT(A2,"0000"),Sheet2!A:B,2,FALSE)
> where there are leading zeros in the text numbers [to 4 digits]
> in the lookup col of the table_array (eg: 0010, 0100, 0002, etc)
>
> Adapt the "0000" part to suit ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

"ViestaWu" wrote:
> Thanks so much Max.
> I tried =vlookup(a2&"", sheet2!A:B,2,FALSE), and it works.

Glad it worked.

> So the reason is, though I set it as text, excel can't recognize it, unless
> I alter it to text obligated, right?

Yes, numbers being matched need to be consistent:
either text vs text, or real vs real
(lookup values vs lookup col in table _array)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---