Only text values matching using index/match lookup - data type pro

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

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?

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