longest prefix match

[rahmanafzal]

Needs help in order to match longest prefix, As an example


I have an excel which contains prefix list like below; Column A contains regions and Column B contains prefix.


Destination Prefix
Afghanistan 93
Afghanistan - Mobile - Afghan Telecom 9375
Afghanistan - Mobile - Awcc 9370
Afghanistan - Mobile - Awcc 9371
Afghanistan - Mobile - Etisalat 9378
Afghanistan - Mobile - Mtn 9376
Afghanistan - Mobile - Mtn 93765
Afghanistan - Mobile - Mtn 93766
Afghanistan - Mobile - Mtn 93767
Afghanistan - Mobile - Mtn 9377
Afghanistan - Mobile - Roshan 9372
Afghanistan - Mobile - Roshan 93744
Afghanistan - Mobile - Roshan 93747
Afghanistan - Mobile - Roshan 9379
Afghanistan - Mobile - Salaam 9374




and Column D contains dialed numbers list, Now what i need is to match the dialed number which is in column D with longest prefix match with column B and return the value of Column A against the match and put in Column E as shown below.


like 93774841931 is longest prefix match 9377 which returns the value "Afghanistan - Mobile - Mtn" in column E.
Dialed Number Region
93774841931 Afghanistan - Mobile - Mtn
93705713317 Afghanistan - Mobile - Awcc
93705713317 Afghanistan - Mobile - Awcc
93787469021 Afghanistan - Mobile - Etisalat
93778513480 Afghanistan - Mobile - Mtn
93778513480 Afghanistan - Mobile - Mtn
93778513480 Afghanistan - Mobile - Mtn
93767195343 Afghanistan - Mobile - Mtn




Regards,
Rahman

 

[Eric W]

Welcome to the Board.

Try:

	A	B	C	D	E	F
1	Destination Prefix
2	Afghanistan	93		93774841931	Afghanistan - Mobile - Mtn
3	Afghanistan - Mobile - Afghan Telecom	9375		93705713317	Afghanistan - Mobile - Awcc
4	Afghanistan - Mobile - Awcc	9370		93705713317	Afghanistan - Mobile - Awcc
5	Afghanistan - Mobile - Awcc	9371		93787469021	Afghanistan - Mobile - Etisalat
6	Afghanistan - Mobile - Etisalat	9378		93778513480	Afghanistan - Mobile - Mtn
7	Afghanistan - Mobile - Mtn	9376		93778513480	Afghanistan - Mobile - Mtn
8	Afghanistan - Mobile - Mtn	93765		93778513480	Afghanistan -Mobile - Mtn
9	Afghanistan - Mobile - Mtn	93766		93767195343	Afghanistan - Mobile - Mtn
10	Afghanistan - Mobile - Mtn	93767
11	Afghanistan - Mobile - Mtn	9377
12	Afghanistan - Mobile - Roshan	9372
13	Afghanistan - Mobile - Roshan	93744
14	Afghanistan - Mobile - Roshan	93747
15	Afghanistan - Mobile - Roshan	9379
16	Afghanistan - Mobile - Salaam	9374

<tbody>
</tbody>

Sheet12​

Array Formulas Cell Formula E2 {=IFERROR(INDEX($A$2:$A$16,MOD(LARGE(IF(LEFT(D2,LEN($B$2:$B$16))=$B$2:$B$16&"",LEN($B$2:$B$16)*10000+ROW($B$2:$B$16)),1),10000)-ROW($B$2)+1),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>