Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




Simple match and link
I've been reading for the last hour or so thru the group and have tried
to modify some of the formulas I thought might help me, but I'm just not getting it. Sheet1 contains names, addresses, reference numbers (Column M), etc. Sheet2 contains codes (Column A) and reference numbers (Column H). I want to have a column in Sheet1 that matches up the reference numbers and then either links to the exact cell in Sheet2 OR pulls in Sheet2's code. One difficulty might be that the reference numbers (Column M) in Sheet1 also contain various other info/numbers. For example: In Spread1 (Column M140), we have: "8489498 2/07 54978  link 3" whereas, Spread2 (Column H3380) would have ONLY the matching number: "8489498." I hope I've made this clear. Not exactly sure what questions to ask so, if you can help, I'll answer any question asap. 
#2
Posted to microsoft.public.excel.worksheet.functions




Simple match and link
Hi!
8489498 2/07 54978  link 3 8489498 Are the reference numbers all the same format? 7 digits long followed by a space and then other characters? Do any of the reference numbers have leading zeros? Are these reference numbers TEXT? Excel see's this as a TEXT string: 8489498 2/07 54978  link 3 And see's this as a NUMBER: 8489498 You can probably start with something like this: =INDEX(Sheet2!A2:A10,MATCH(LEFT(M2,7),Sheet2!H2:H10,0)) Biff "motol" wrote in message ups.com... I've been reading for the last hour or so thru the group and have tried to modify some of the formulas I thought might help me, but I'm just not getting it. Sheet1 contains names, addresses, reference numbers (Column M), etc. Sheet2 contains codes (Column A) and reference numbers (Column H). I want to have a column in Sheet1 that matches up the reference numbers and then either links to the exact cell in Sheet2 OR pulls in Sheet2's code. One difficulty might be that the reference numbers (Column M) in Sheet1 also contain various other info/numbers. For example: In Spread1 (Column M140), we have: "8489498 2/07 54978  link 3" whereas, Spread2 (Column H3380) would have ONLY the matching number: "8489498." I hope I've made this clear. Not exactly sure what questions to ask so, if you can help, I'll answer any question asap. 
#3
Posted to microsoft.public.excel.worksheet.functions




Simple match and link
Biff:
Might you need a "value" function for the "left" function, to derive the numeric value to accurately compare? Also, I've spent some time reading through these messages. I notice on a some of your posts you put "" [such as "(LEFT(M2,7)..."]. What does "" mean? Thanks. "Biff" wrote: Hi! 8489498 2/07 54978  link 3 8489498 Are the reference numbers all the same format? 7 digits long followed by a space and then other characters? Do any of the reference numbers have leading zeros? Are these reference numbers TEXT? Excel see's this as a TEXT string: 8489498 2/07 54978  link 3 And see's this as a NUMBER: 8489498 You can probably start with something like this: =INDEX(Sheet2!A2:A10,MATCH(LEFT(M2,7),Sheet2!H2:H10,0)) Biff "motol" wrote in message ups.com... I've been reading for the last hour or so thru the group and have tried to modify some of the formulas I thought might help me, but I'm just not getting it. Sheet1 contains names, addresses, reference numbers (Column M), etc. Sheet2 contains codes (Column A) and reference numbers (Column H). I want to have a column in Sheet1 that matches up the reference numbers and then either links to the exact cell in Sheet2 OR pulls in Sheet2's code. One difficulty might be that the reference numbers (Column M) in Sheet1 also contain various other info/numbers. For example: In Spread1 (Column M140), we have: "8489498 2/07 54978  link 3" whereas, Spread2 (Column H3380) would have ONLY the matching number: "8489498." I hope I've made this clear. Not exactly sure what questions to ask so, if you can help, I'll answer any question asap. 
#4
Posted to microsoft.public.excel.worksheet.functions




Simple match and link
"bman342" wrote in message
... Biff: Might you need a "value" function for the "left" function, to derive the numeric value to accurately compare? Also, I've spent some time reading through these messages. I notice on a some of your posts you put "" [such as "(LEFT(M2,7)..."]. What does "" mean? Thanks. That's what the double unary does, it converts a TEXT number into a NUMERIC number. Since the LEFT function returns a TEXT value the "" comverts the result to a NUMERIC number. You can do the same thing using any of these methods: VALUE(LEFT(M2,7)) 1*LEFT(M2,7) 0+LEFT(M2,7) LEFT(M2,7) I just use the double unary as personal preference. Biff 
#5
Posted to microsoft.public.excel.worksheet.functions




Simple match and link
Thanks for the reply. I managed to figure it out! Thought I'd post it
in case it might help someone else. Or, if anyone sees a way I can clean it up a bit if it's redundant... =INDEX(codes!$A$2:$B$4001,MATCH($M3491,codes!$A$2: $A$4001,0),2) Biff wrote: "bman342" wrote in message ... Biff: Might you need a "value" function for the "left" function, to derive the numeric value to accurately compare? Also, I've spent some time reading through these messages. I notice on a some of your posts you put "" [such as "(LEFT(M2,7)..."]. What does "" mean? Thanks. That's what the double unary does, it converts a TEXT number into a NUMERIC number. Since the LEFT function returns a TEXT value the "" comverts the result to a NUMERIC number. You can do the same thing using any of these methods: VALUE(LEFT(M2,7)) 1*LEFT(M2,7) 0+LEFT(M2,7) LEFT(M2,7) I just use the double unary as personal preference. Biff 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Double and Multiple Lookup Using the MATCH Function  Excel Worksheet Functions  
Formula to Link to Specific List on Another Worksheet  Excel Worksheet Functions  
How can I make the reference link match the new formatting of its  Excel Discussion (Misc queries)  
Paste Link  retaining formatting  Excel Discussion (Misc queries)  
index & match with links for updating to other workbooks  Excel Worksheet Functions 