Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Number for V Lookup
I am attempting a V Lookup which involves Employee ID#'s, of which some begin
with a Letter (A123) and some begin with a Zero (0123). When I attempt to convert these to Numbers I see no change and my V Lookup returns a #N/A error. Any suggestions would be greatly appreciated. Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Number for V Lookup
For the 2, "numbers" that you mention (A123 and 0123) what are you trying to
return from the VLOOKUP? Just the number 123 for both of them? "PeterB" wrote: I am attempting a V Lookup which involves Employee ID#'s, of which some begin with a Letter (A123) and some begin with a Zero (0123). When I attempt to convert these to Numbers I see no change and my V Lookup returns a #N/A error. Any suggestions would be greatly appreciated. Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Number for V Lookup
I am trying to return the whole "number" (A123 and 0123). I have one
spreadsheet with Employee #s which matches them to their Manager. I have to update this information on another spreadsheet. Thank you "trip_to_tokyo" wrote: For the 2, "numbers" that you mention (A123 and 0123) what are you trying to return from the VLOOKUP? Just the number 123 for both of them? "PeterB" wrote: I am attempting a V Lookup which involves Employee ID#'s, of which some begin with a Letter (A123) and some begin with a Zero (0123). When I attempt to convert these to Numbers I see no change and my V Lookup returns a #N/A error. Any suggestions would be greatly appreciated. Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Number for V Lookup
I have just put a file for you at:-
http://www.pierrefondes.com/ Item number 37 towards the top of my home page. (Ignore the smith smith smith in the cells because these were done for somebody else). As you can see 123 gets returned in cells F 14 and F 15 (I think that this is what you want). If my comments have helped please hit Yes. Thanks. "PeterB" wrote: I am attempting a V Lookup which involves Employee ID#'s, of which some begin with a Letter (A123) and some begin with a Zero (0123). When I attempt to convert these to Numbers I see no change and my V Lookup returns a #N/A error. Any suggestions would be greatly appreciated. Thank you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Number for V Lookup
I'm sorry, I don't think I explained this correctly originally. I am actually
trying to pull the Manager Name data from one sheet into another by way of the their Employees' ID#'s, which range from 0001 to K999. The formula I am using right now is: =VLOOKUP(E2,'[December AM Master (2).xlsx]Page1_1'!$B:$L,11,0) On the December AM Master sheet Column B is the Employees' ID#'s and L is their Managers' Names. I have the Employee's ID#'s on the other sheet but not the Managers' Names which is why I am attempting this VLOOKUP, to add their Names to the sheet. I'm not sure why but only some of the Manager Names are copying over. "trip_to_tokyo" wrote: I have just put a file for you at:- http://www.pierrefondes.com/ Item number 37 towards the top of my home page. (Ignore the smith smith smith in the cells because these were done for somebody else). As you can see 123 gets returned in cells F 14 and F 15 (I think that this is what you want). If my comments have helped please hit Yes. Thanks. "PeterB" wrote: I am attempting a V Lookup which involves Employee ID#'s, of which some begin with a Letter (A123) and some begin with a Zero (0123). When I attempt to convert these to Numbers I see no change and my V Lookup returns a #N/A error. Any suggestions would be greatly appreciated. Thank you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Number for V Lookup
The employee ID entries on both sheets much match exactly and must be of the
same 'type'. For instance, lets say on one sheet you have formatted the column containing the IDs as text so when you entered 0001, it was accepted as text and actually consists for 3-zero characters and a 1. But over on the other sheet, you've set the format for the column's numbers (using format cell) to ####, then you enter a 1 and it appears as 0001, but underneath it is really only "1", so when you try to match "0001" to what appears to be "0001" it's actually trying to match "0001" against "1" and you get a #N/A! error. You're going to have to assure consistency of types and contents for those employee IDs that don't actually start with a letter of the alphabet. "PeterB" wrote: I'm sorry, I don't think I explained this correctly originally. I am actually trying to pull the Manager Name data from one sheet into another by way of the their Employees' ID#'s, which range from 0001 to K999. The formula I am using right now is: =VLOOKUP(E2,'[December AM Master (2).xlsx]Page1_1'!$B:$L,11,0) On the December AM Master sheet Column B is the Employees' ID#'s and L is their Managers' Names. I have the Employee's ID#'s on the other sheet but not the Managers' Names which is why I am attempting this VLOOKUP, to add their Names to the sheet. I'm not sure why but only some of the Manager Names are copying over. "trip_to_tokyo" wrote: I have just put a file for you at:- http://www.pierrefondes.com/ Item number 37 towards the top of my home page. (Ignore the smith smith smith in the cells because these were done for somebody else). As you can see 123 gets returned in cells F 14 and F 15 (I think that this is what you want). If my comments have helped please hit Yes. Thanks. "PeterB" wrote: I am attempting a V Lookup which involves Employee ID#'s, of which some begin with a Letter (A123) and some begin with a Zero (0123). When I attempt to convert these to Numbers I see no change and my V Lookup returns a #N/A error. Any suggestions would be greatly appreciated. Thank you |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting to Number for V Lookup
Hi Peter.
I have just spent some more time testing your issue: as far as I can see it is a reproducible error in EXCEL 2007. I have tried to match your inputs exactly. If I format Employee ID Number and Manager Name columns in both Worksheets as text there are circumstances in which #N/A being returned. If I then change the format of both columns in both Worksheets as General the situation seems to be worse in that I get more columns being returned as #N/A. I think that this is a software error that Microsoft need to fix (I can prepare detailed Test Cases for Microsoft if required). Without this fix in place you need to work out a work-around (which, at this point in time, I have not considered). Please hit Yes if my comments have helped. Thanks. "PeterB" wrote: I'm sorry, I don't think I explained this correctly originally. I am actually trying to pull the Manager Name data from one sheet into another by way of the their Employees' ID#'s, which range from 0001 to K999. The formula I am using right now is: =VLOOKUP(E2,'[December AM Master (2).xlsx]Page1_1'!$B:$L,11,0) On the December AM Master sheet Column B is the Employees' ID#'s and L is their Managers' Names. I have the Employee's ID#'s on the other sheet but not the Managers' Names which is why I am attempting this VLOOKUP, to add their Names to the sheet. I'm not sure why but only some of the Manager Names are copying over. "trip_to_tokyo" wrote: I have just put a file for you at:- http://www.pierrefondes.com/ Item number 37 towards the top of my home page. (Ignore the smith smith smith in the cells because these were done for somebody else). As you can see 123 gets returned in cells F 14 and F 15 (I think that this is what you want). If my comments have helped please hit Yes. Thanks. "PeterB" wrote: I am attempting a V Lookup which involves Employee ID#'s, of which some begin with a Letter (A123) and some begin with a Zero (0123). When I attempt to convert these to Numbers I see no change and my V Lookup returns a #N/A error. Any suggestions would be greatly appreciated. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Negative Number to Postive number | Excel Worksheet Functions | |||
converting a positive number to neg number in spreadsheets | Excel Discussion (Misc queries) | |||
Converting a negative number to a positive number | Excel Discussion (Misc queries) | |||
Converting A Number To Text | Excel Discussion (Misc queries) | |||
converting number string to number with decimal | Excel Worksheet Functions |