How can I do a "best match" for text entries?

[Guest]

I am analyzing our company's telephone call records in raw text format - all
I have is the telephone number that was dialed by the caller. With the help
of a master reference list I created listing all dialing patterns possible
and the related location name for each, I am trying to associate a
country/city/mobile description to each phone call record to know the
geographic location called. What I would want to do is find the location
name in Sheet 2 for each call record in Sheet 1 using the dialing pattern
that matches it best (from left-most to right-most text position in Sheet 1).

Here's an example:
Sheet 1 - Our call records
Call #1: 52512346789
Call #2: 33189895656
Call #3: 966512345555
Call #4: 33845457777
Call #5: 33485556666

Sheet 2 - My reference list (in numerical order per country)
-start of file-
1 - USA
1212 - USA (New York)
....
33 - France
331 - France (Paris)
336 - France (mobile)
....
52 - Mexico
525 - Mexico (Mexico City)
....
966 - Saudi Arabia
9965 - Saudi Arabia (mobile)
....
998 - Uzbekistan
-end of file-

Sheet 3 - My desired result
Call #1: 52512346789 - Mexico (Mexico City)
Call #2: 33189895656 - France (Paris)
Call #3: 966512345555 - Saudi Arabia (mobile)
Call #4: 33845457777 - France
Call #5: 33485556666 - France

Appreciate any info you can share.

 

[Trevor Shuttleworth]

Assuming the data starts in row 1, put the following formula in cell C1, or
wherever:

=IF(ISNA(VLOOKUP(--LEFT(B1,4),Sheet2!A:B,2,FALSE)),
IF(ISNA(VLOOKUP(--LEFT(B1,3),Sheet2!A:B,2,FALSE)),IF(ISNA(VLOOKUP(--LEFT(B1,2),Sheet2!A:B,2,FALSE)),VLOOKUP(--LEFT(B1,1),Sheet2!A:B,2,FALSE),VLOOKUP(--LEFT(B1,2),Sheet2!A:B,2,FALSE)),VLOOKUP(--LEFT(B1,3),Sheet2!A:B,2,FALSE)),
VLOOKUP(--LEFT(B1,4),Sheet2!A:B,2,FALSE))

This should all be on one line so watch the word wrap.

The formula works for your sample data, although the third example is wrong.
It should be Saudi Arabia, not Saudi Arabia (mobile). It looks as though
the entry in the reference table is wrong and should be 9665 and not 9965

Regards

Trevor