INDEX MATCH Troubleshooting

[d dubya]

Hello all;
I am creating a spreadsheet that links to a workbook on our shared drive. Column A contains data such as below, which equal to A213 and A214:

415075_CUNNINGHAM
415080_OAKWOOD

I am indexing and matching to the 6 digit number in the cells in this column. My problem is that the only way I get a return from A1:A213 is with the following formula:
=INDEX('List'!$A$1:$A$3000,MATCH(A2&"*",'List'!$D$3:$D$3000,0),1) which looks for the 6 digit number and any text behind it.

The above formula does not work from A214 and down. The only thing I have gotten to work is:
=INDEX('List'!$A$3:$A$3000,MATCH($A$2,'List'!$D$3:$D$3000,0),1)

I have tried formatting the cells in columns A and D of the workbook to all "general" and all "text", but that doesn't work. I cannot attach the spreadsheet due to corporate regulations.

Any suggestions? I've never had to troubleshoot anything like this.

Thanks,
Dave

[FDibbins]

Formatting does not change the contents of a cell, only the way it shows...it is a cosmetic change.

What do you have in A2? I notice in the 2nd formula, you have that absoluted ($), but not in the 1st formula - was that a typo?

[d dubya]

No it was not a typo- it's the only way that works to retrieve valid data on rows 214 and down in the workbook I am linking to.

[FDibbins]

I think you will need to create a dummy file to show us samples of what you are working with - and what you want

[d dubya]

OK- I will do so as soon as I have the time.
Thanks for your help.