Vlookup question

[Guest]

I have a Vlookup formula that is returning an #N/A for the value of the
formula. Now the formula is written correctly but it only works when I
manually retype the value on the sheet I am trying to return the formula to.

In other words, if I have a value of 1234 on one sheet and value 1234 on the
other sheet is what im trying to match against to return value ABCD

The initial formula will look like so:

Value Return Value

1234 #N/A

When I manually type over the 1234 it will return Value ABCD instead of the
#N/A.

I checked all of the general formatting and it didn't change it, is there
anything im missing?

Thanks,

Bob

 

[Guest]

If the formatting is correct, could there be an extra "hidden" e.g. blank,
character at the end of the field you are overtyping?

Try TRIM and/OR CLEAN on a cell which is giving a problem to see if this
solves the problem.

HTH

 

[JE McGimpsey]

Sounds like your Value is entered as Text (which sometimes happens when
you paste in from a browser or other application). Text doesn't match
numbers. When you type over, a number is entered.

If you have many values like this, you can coerce them to numbers by
copying an empty cell, selecting your "numbers", then choosing
Edit/Paste Special, selecting the Values and Add radio buttons.

 

[Peo Sjoblom]

Assuming it is the lookup value you need to retype so it must be that the
lookup value(s) are seen as text until you type them again


Try this, copy an empty cell formatted as general, then select the lookup
value(s) and do edit>paste special and select add. Does it work now? If not
you might have html characters in the cell (is it imported from the web?)

Then you can try this macro on the lookup values

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


same website has instruction on how to install macros


--


Regards,


Peo Sjoblom