VLookup not recognising numbers
 

I have a large lookup table that returns a product code.Columns A-C and E-G
are from different spreadsheets
For some reason it is recognising some of the numbers that have a letter as
a prefix,(eg A10786),but not just numbers. ,I have tried copying the
format,re-formatting etc,but the only thing that works is actually keying in
the number again ,(in C6 when i manually input 1039, 815345099 correctly
appears)
This is a 5000 long row lookup so I need to try and find a solution.

A B C D E
F G
Acc Ref lookup ref Rep Acc Name
1 1039 815345099 #N/A JX 422 J Smith
2 1045 937884746 #N/A GA 467 J Brown
3 1086 817207341 #N/A DA 478 G Green
4 1092 876415588 #N/A HX 487 B Blue
5 1101 736190348 #N/A AX 509 R red
6 1111 784520457 #N/A AX 1039 P White


I am familier with lookups and have never had this before.Can anyone help ?

Doug Morgan

your "1039" is proabably not being recognzed as a number.
you probably have a formula that extracts this from some other form
try
=value(trim(yourformula))
in the cell referenced by the lookup in the Vlookup equation)

"Doug" wrote:

I have a large lookup table that returns a product code.Columns A-C and E-G
are from different spreadsheets
For some reason it is recognising some of the numbers that have a letter as
a prefix,(eg A10786),but not just numbers. ,I have tried copying the
format,re-formatting etc,but the only thing that works is actually keying in
the number again ,(in C6 when i manually input 1039, 815345099 correctly
appears)
This is a 5000 long row lookup so I need to try and find a solution.

A B C D E
F G
Acc Ref lookup ref Rep Acc Name
1 1039 815345099 #N/A JX 422 J Smith
2 1045 937884746 #N/A GA 467 J Brown
3 1086 817207341 #N/A DA 478 G Green
4 1092 876415588 #N/A HX 487 B Blue
5 1101 736190348 #N/A AX 509 R red
6 1111 784520457 #N/A AX 1039 P White


I am familier with lookups and have never had this before.Can anyone help ?

Doug Morgan

Hi
You might be mixing up numbers and 'text' numbers. To make 'text' numbers
into proper numbers, copy a blank unused cell, select your range of
'numbers' and go to Edit|Paste Special|Add.

--
Andy.


"Doug" wrote in message
...
I have a large lookup table that returns a product code.Columns A-C and E-G
are from different spreadsheets
For some reason it is recognising some of the numbers that have a letter
as
a prefix,(eg A10786),but not just numbers. ,I have tried copying the
format,re-formatting etc,but the only thing that works is actually keying
in
the number again ,(in C6 when i manually input 1039, 815345099 correctly
appears)
This is a 5000 long row lookup so I need to try and find a solution.

A B C D E
F G
Acc Ref lookup ref Rep Acc Name
1 1039 815345099 #N/A JX 422 J Smith
2 1045 937884746 #N/A GA 467 J Brown
3 1086 817207341 #N/A DA 478 G Green
4 1092 876415588 #N/A HX 487 B Blue
5 1101 736190348 #N/A AX 509 R red
6 1111 784520457 #N/A AX 1039 P White


I am familier with lookups and have never had this before.Can anyone help
?

Doug Morgan

Did you mean this ?

=VALUE(TRIM(VLOOKUP(G2,$A$2:$B$3973,2,FALSE)))

"bj" wrote:

your "1039" is proabably not being recognzed as a number.
you probably have a formula that extracts this from some other form
try
=value(trim(yourformula))
in the cell referenced by the lookup in the Vlookup equation)

"Doug" wrote:

I have a large lookup table that returns a product code.Columns A-C and E-G
are from different spreadsheets
For some reason it is recognising some of the numbers that have a letter as
a prefix,(eg A10786),but not just numbers. ,I have tried copying the
format,re-formatting etc,but the only thing that works is actually keying in
the number again ,(in C6 when i manually input 1039, 815345099 correctly
appears)
This is a 5000 long row lookup so I need to try and find a solution.

A B C D E
F G
Acc Ref lookup ref Rep Acc Name
1 1039 815345099 #N/A JX 422 J Smith
2 1045 937884746 #N/A GA 467 J Brown
3 1086 817207341 #N/A DA 478 G Green
4 1092 876415588 #N/A HX 487 B Blue
5 1101 736190348 #N/A AX 509 R red
6 1111 784520457 #N/A AX 1039 P White


I am familier with lookups and have never had this before.Can anyone help ?

Doug Morgan

Instead of say: =VLOOKUP(B2,Sheet2!A:B,2,0)

Try:

=VLOOKUP(B2+0,Sheet2!A:B,2,0)
(coerces text numbers in col B to real numbers)

or

=VLOOKUP(TEXT(B2,"0000"),Sheet2!A:B,2,0)
(converts real numbers in col B to text numbers)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Doug" wrote in message
...
I have a large lookup table that returns a product code.Columns A-C and
E-G
are from different spreadsheets
For some reason it is recognising some of the numbers that have a letter
as
a prefix,(eg A10786),but not just numbers. ,I have tried copying the
format,re-formatting etc,but the only thing that works is actually keying
in
the number again ,(in C6 when i manually input 1039, 815345099 correctly
appears)
This is a 5000 long row lookup so I need to try and find a solution.

A B C D E
F G
Acc Ref lookup ref Rep Acc Name
1 1039 815345099 #N/A JX 422 J Smith
2 1045 937884746 #N/A GA 467 J Brown
3 1086 817207341 #N/A DA 478 G Green
4 1092 876415588 #N/A HX 487 B Blue
5 1101 736190348 #N/A AX 509 R red
6 1111 784520457 #N/A AX 1039 P White


I am familier with lookups and have never had this before.Can anyone help
?

Doug Morgan

Sorted,

Thanks Andy, much apprieciated.

"Andy B" wrote:

Hi
You might be mixing up numbers and 'text' numbers. To make 'text' numbers
into proper numbers, copy a blank unused cell, select your range of
'numbers' and go to Edit|Paste Special|Add.

--
Andy.


"Doug" wrote in message
...
I have a large lookup table that returns a product code.Columns A-C and E-G
are from different spreadsheets
For some reason it is recognising some of the numbers that have a letter
as
a prefix,(eg A10786),but not just numbers. ,I have tried copying the
format,re-formatting etc,but the only thing that works is actually keying
in
the number again ,(in C6 when i manually input 1039, 815345099 correctly
appears)
This is a 5000 long row lookup so I need to try and find a solution.

A B C D E
F G
Acc Ref lookup ref Rep Acc Name
1 1039 815345099 #N/A JX 422 J Smith
2 1045 937884746 #N/A GA 467 J Brown
3 1086 817207341 #N/A DA 478 G Green
4 1092 876415588 #N/A HX 487 B Blue
5 1101 736190348 #N/A AX 509 R red
6 1111 784520457 #N/A AX 1039 P White


I am familier with lookups and have never had this before.Can anyone help
?

Doug Morgan

Glad to help and thanks for the feedback!

--
Andy.


"Doug" wrote in message
...
Sorted,

Thanks Andy, much apprieciated.

"Andy B" wrote:

Hi
You might be mixing up numbers and 'text' numbers. To make 'text' numbers
into proper numbers, copy a blank unused cell, select your range of
'numbers' and go to Edit|Paste Special|Add.

--
Andy.


"Doug" wrote in message
...
I have a large lookup table that returns a product code.Columns A-C and
E-G
are from different spreadsheets
For some reason it is recognising some of the numbers that have a
letter
as
a prefix,(eg A10786),but not just numbers. ,I have tried copying the
format,re-formatting etc,but the only thing that works is actually
keying
in
the number again ,(in C6 when i manually input 1039, 815345099
correctly
appears)
This is a 5000 long row lookup so I need to try and find a solution.

A B C D E
F G
Acc Ref lookup ref Rep Acc Name
1 1039 815345099 #N/A JX 422 J Smith
2 1045 937884746 #N/A GA 467 J Brown
3 1086 817207341 #N/A DA 478 G Green
4 1092 876415588 #N/A HX 487 B Blue
5 1101 736190348 #N/A AX 509 R red
6 1111 784520457 #N/A AX 1039 P White


I am familier with lookups and have never had this before.Can anyone
help
?

Doug Morgan

=VLOOKUP(VALUE(TRIM(G2)),$A$2:$B$3973,2,FALSE)

"Doug" wrote:

Did you mean this ?

=VALUE(TRIM(VLOOKUP(G2,$A$2:$B$3973,2,FALSE)))

"bj" wrote:

your "1039" is proabably not being recognzed as a number.
you probably have a formula that extracts this from some other form
try
=value(trim(yourformula))
in the cell referenced by the lookup in the Vlookup equation)

"Doug" wrote:

I have a large lookup table that returns a product code.Columns A-C and E-G
are from different spreadsheets
For some reason it is recognising some of the numbers that have a letter as
a prefix,(eg A10786),but not just numbers. ,I have tried copying the
format,re-formatting etc,but the only thing that works is actually keying in
the number again ,(in C6 when i manually input 1039, 815345099 correctly
appears)
This is a 5000 long row lookup so I need to try and find a solution.

A B C D E
F G
Acc Ref lookup ref Rep Acc Name
1 1039 815345099 #N/A JX 422 J Smith
2 1045 937884746 #N/A GA 467 J Brown
3 1086 817207341 #N/A DA 478 G Green
4 1092 876415588 #N/A HX 487 B Blue
5 1101 736190348 #N/A AX 509 R red
6 1111 784520457 #N/A AX 1039 P White


I am familier with lookups and have never had this before.Can anyone help ?

Doug Morgan