I run huge Access queries for exporting data into Excel worksheets. When I then try to use Excel's Index function (to lookup 3 char text codes (i.e., "ABC") from "Access imported" worksheet column C to return text in column D (i.e., "alpha beta cigma") index fails with error #N/A.
It seems that the imported Access text "ABC" in worksheet Data1 does not match my Excel entered text "ABC" in worksheet Data2. Only work around so far is to copy cell "ABC" from Data1 and paste over current Data2's "ABC" - rather exhaustive work for 64000 rows...then it recognizes it as text "ABC".
There are no leading/following spaces, the column is sorted - it is simple "ABC" text match error.
Tried to reformat column in Data1 to "text"... I've run out of ideas. If it is a reference error, not a visible one. I use the Index function successfully 99% of time...just not today. :?