INDEX/MATCH problem when looking up data with various prefixes (letters and numbers)...

[bcd-at-work]

In one workbook, I have a working sheet, and a lookup sheet containing employee information.

I have an INDEX/MATCH formula which will look up the Associate ID, and return the first and last name of the employee. Basically, it's:
=INDEX(lookup!D:D,MATCH(D2,lookup!C:C,0))

The format of our Associate ID's can vary:

• sometimes starts with a zero (example: 84062)
• sometimes starts with a different digit (example: 08462)
• sometimes starts with a letter(example: O8462)

On my working sheet, sometimes I can type the Associate ID# in manually, and the formula works fine. Other times I'm pasting from an unformatted .txt list of hundreds of Associate ID#s. When clicking on any one of my pasted cells, it looks identical to any other cell (i.e., no extra spaces, same cell format), however only about 1 our of every 20 actually performs the lookup correctly (the others find nothing). Clicking manually calculate does not yield different results. However, if I type the EXACT same ID# on top, rewriting the cell, it DOES calculate correctly. What's up with that?

What format should the Associate ID columns be, both in the lookup sheet and the working sheet? General, Number, Text, Special, Custom, or otherwise? Or do I need to give up, and remember why I chose INDEX/MATCH over VLOOKUP?

Thank you in advance!

[bcd-at-work]

I THINK the =TRIM command may have worked... testing it on various cells now.

[bcd-at-work]

I spoke too soon. As soon as I saved, and returned to the workbook later, the same problem occurs. =TRIM doesn't seem to be working anymore.

[bcd-at-work]

Additionally, every time I hit "calculate sheet," it seems to find and then not find different data! i.e., on my last F9 it successfully looked up one row, row 2, out of the first 20. Did it again, and row 2 turned back to #N/A's... but it successfully looked up row 3.