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?

How to Activate Vlookup Function?
 

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?

How to Activate Vlookup Function?
 

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?

How to Activate Vlookup Function?
 

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

How to Activate Vlookup Function?
 

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

How to Activate Vlookup Function?
 

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

How to Activate Vlookup Function?
 

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