Match or Vlookup numbers with most leading digits being the same. 2

[telecomadmin12]

In Excel I am trying to match numbers against a list containing numbers and I am looking for the closest match meaning the most leading digits being the same.
Let's say I have a number 1234002934 and a list of other numbers that looks like this:
12340029
12340028
1234002
12340
123
12
1

12340029 in the list is the closest match to the number 1234002934. The most leading digits are the same.
Then I might have another number e.g. 125555555. The closest match for that in the list would be 12.

How do I achieve that with an excel function?
Vlookup would be best if possible.

Thanks.

 

[mintjulep]

Then I might have another number e.g. 125555555. The closest match for that in the list would be 12.

Why?

The first 6 numbers on your list all have the first 2 numbers matching.

 

[SkipVought]

Hi,

first I named my Range MyList
[pre]
MyList
9999999999
12340029
12340028
1234002
12340
123
12
1
[/pre]

second add a MAX VALUE 9999999999.

third the range Number Format needs to be changed to TEXT

forth EDIT every value since just changing the number format does not change the underlying value which must be text.

this is your formula...
[tt]
=INDEX(MyList,MATCH([highlight #FCE94F]A1[/highlight],MyList,-1))
[/tt]
...if your test value is in [highlight #FCE94F]A1[/highlight]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[telecomadmin12]

Why? The first 6 numbers on your list all have the first 2 numbers matching.]

For 125555555 only "12" in the list would be considered a match. The others aren't cause they contain numbers that don't match (except number "1", but "12" is a closer match).

12340029
12340028
1234002
12340
123
12
1

I have a list of call records. A voice service provider has sent me a rate sheet which I want to match my called numbers against to find out how much I would be paying if I went with the new service.

So for a dialed number there is only one match in the list, and that is the one where the most leading digits overlap.
My dialed numbers are all 10 or more digits long.
One dialed number would e.g. 2128736342. That's NY for a 212 area code.
On my new providers list there are more entries that cover that area code but my number will match only one.
E.g.:

212 1c per minute
21287 1.1c per minute
21287363 0.9c per minute
21286231 0.8c per minute

My dialed number matches "21287363 0.9c per minute". That's the closest where the first 8 digits are the same. For number 2129813452 the match would be "212 1c per minute"

 

[SkipVought]

 

[SkipVought]

 

[SkipVought]

I wrote a user defined function (UDF) VBA.

The logic is something that native Excel functions cannot perform.

Function FindRate(sPhone As String) As Single
    Dim i As Integer, lOff As Variant, sLook As String
    Dim bFound As Boolean
    
    bFound = False
    
    For i = 1 To Len(sPhone)
        sLook = Left(sPhone, Len(sPhone) - i + 1)
        lOff = Application.Match(sLook, [MyList], 0)
        If IsError(lOff) Then
            Do
                sLook = Left(sPhone, Len(sPhone) - i + 2)
                lOff = Application.Match(sLook, [MyList], 0)
                If IsError(lOff) Then
                    If bFound Then
                        i = i - 1
                    Else
                        i = i + 1
                    End If
                Else
                    Exit For
                End If
            Loop
        Else
            Exit For
        End If
        bFound = True
    Next
    
    FindRate = Range("Rate")(lOff)
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]