Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup question
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup question
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 "Bob" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup question
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. In article , Bob wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup question
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 editpaste 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 "Bob" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup question | Excel Worksheet Functions | |||
Vlookup question | Excel Discussion (Misc queries) | |||
VLOOKUP question | Excel Discussion (Misc queries) | |||
VLOOKUP Question.. | Excel Discussion (Misc queries) | |||
VLOOKUP question | Excel Worksheet Functions |