Excel sees a difference between 10154 and '10154 (number numbers vs text
numbers).
My bet is you have some text numbers and number numbers in the range (or both
ranges).
One way to fix this (convert them all to number numbers).
select an empty cell
Edit|copy
select that range (whole column???) and
edit|paste special|check Add.
(I'd do both ranges if I were you.)
Stanton wrote:
>
> I am using index(match()) to link two sets of data together. The key column
> is alpha-numeric in the format 000000A where the last character can be an X
> or a number.
>
> E.g: In the lookup worksheet the values are:
>
> 044507X 50000537
> 044510X 50001148
> 10111 50000533
> 10154 50000459
> 10162 50001154
>
> The problem is, I am only getting results where the matching values contain
> an X as the final character all the rest product #N/A.
>
> I beleive this to be a data type problem and have found some advice about
> changing registry values (ImportMixedTypes = Text, TypeGuessRows = 0, IMEX =
> 1) to stop Excel making incorrect assumptions about data types.
>
> Problem is my desktop is locked-down so that I cannot change the registry.
>
> Is there a way I can control Excel's data typing directly using a formula?
--
Dave Peterson
Bookmarks