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!
Bookmarks