Vlookup Function Problem

[donnychan]

I have a Excel file with one calculation sheet contains a VLOOKU
FUNCTION column and several data sheets. when I copy\past
special\value a new set of data to the data sheet, the VLOOKUP FUNCTIO
column returns N/A. after I have manually re-enter all the look_u
value data in the updated data sheet, the VLOOKUP FUNCTION COLUMN the
return the correct result. Would some one please help to let me kno
why it happened and how can I avoid such re-entering tedious job?

Many thanks in advance!

:confused

 

[Vasant Nanavati]

Is your data all-numeric? VLOOKUP often gets confused between numeric data
and numbers formatted as text. Can you post some examples of your data and
the formula?

 

[Ragdyer]

Is the lookup data supposed to be numbers?

If yes, then maybe the "Pasted / Values" is "Text" that just looks like
numbers.

If this might be the case, you can try this to convert all the 'Pasted" data
to numbers in a single procedure.
Right click in a new, unused, empty cell and choose "Copy".

Now, select all the new, pasted data, and right click in this selection.
Choose "PasteSpecial", and then click on "Add", then <OK>.

This should convert all your cells to "General", where numbers are numbers,
and text is text.

 

[donnychan]

Dear Vasant,

I have used "text" format for both data and lookup value. I hav
Attached a very simple file named"vlookup-test.xls" to illustrate m
query.

The result of the vlookup function when you open the file is "N/A". I
you change the Lookup Value to any one value that under column A, yo
still get the same result "N/A". If you manually re-type the value (yo
selected in Lookup Value) in column A, then you get the correc
answer.

Strange? Any idea of why?


Thanks for your help!

Donny:

 

[donnychan]

Why my sample file not shown on the post? I try to attach it again.

Attachment filename: vlookup-test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=627410

 

[Steved]

Hello Donny

I Changed Column E to General Format, and it fuctioned as it should.

Cheers.

 

[donnychan]

Steved,

Thanks for your help!


Regards/Donn