Converting to Number for V Lookup

[PeterB]

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

 

[trip_to_tokyo]

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]

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]

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]

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.

 

[JLatham]

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.

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.

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.

 

[trip_to_tokyo]

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.

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.

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.