Pesky VLookup !!!

[Guest]

I have two sheets, and a vlookup to pull a contact name from one based on a 6
digit number. I get an N/A error all the time. BUT if i manually cut and past
the lookup value into the other sheet and overwrite the same number, the
formula works. I have tried copying formatting from one to the other etc etc
and it still won't work unless i copy the number across. Both files are the
same version. If i copy the number to get it to work, but then overtype with
the same number, it fails again !

Any ideas?

 

[JulieD]

Hi

trailing spaces? calculation set to manual? VLOOKUP function without fourth
parameter set to FALSE ... would be my first guesses -
are we talking about two sheets or two workbooks?

.... if all else fails, if you'ld like to zip the file up and email direct to
me, i'll be happy to take a look (julied at hcts dot net dot au)

cheers
JulieD

 

[Don S]

I have two sheets, and a vlookup to pull a contact name from one based on a 6
digit number. I get an N/A error all the time. BUT if i manually cut and past
the lookup value into the other sheet and overwrite the same number, the
formula works. I have tried copying formatting from one to the other etc etc
and it still won't work unless i copy the number across. Both files are the
same version. If i copy the number to get it to work, but then overtype with
the same number, it fails again !

Any ideas?

Check the cell format in both sheets. If it isn't identical, that will
happen. ie: one formatted as text and the other numeric. Since your
source data may change regularly, copy and paste its format in the
lookup table.

Don S

 

[Guest]

I had a similar problem and discovered that there was a noise level in my
lookup number past the display. I changed the lookup cells to a rounded
number with zero decimals and the problems went away.

 

[Guest]

Are the range cells locked in your vlookup formula? Hit F4 to randomly role
through the "cell lock" choises. If your hunting numbers, try multiplying
them by 1 in another colum and then Vlookup from the result. If your hunting
text, try utilizing the CLEAN formula. -c