View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff Biff is offline
external usenet poster
 
Posts: 1,688
Default 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.