Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP for a cell with both letters and numbers | Excel Discussion (Misc queries) | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
how to format numbers stored as text or vice versa to use vlookup | Excel Worksheet Functions |