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
Destination Prefix
Afghanistan	93774841931	Afghanistan - Mobile - Mtn
Afghanistan - Mobile - Afghan Telecom	93705713317	Afghanistan - Mobile - Awcc
Afghanistan - Mobile - Awcc	93705713317	Afghanistan - Mobile - Awcc
Afghanistan - Mobile - Awcc	93787469021	Afghanistan - Mobile - Etisalat
Afghanistan - Mobile - Etisalat	93778513480	Afghanistan - Mobile - Mtn
Afghanistan - Mobile - Mtn	93778513480	Afghanistan - Mobile - Mtn
Afghanistan - Mobile - Mtn	93778513480	Afghanistan -Mobile - Mtn
Afghanistan - Mobile - Mtn	93767195343	Afghanistan - Mobile - Mtn
Afghanistan - Mobile - Mtn
Afghanistan - Mobile - Mtn
Afghanistan - Mobile - Roshan
Afghanistan - Mobile - Roshan
Afghanistan - Mobile - Roshan
Afghanistan - Mobile - Roshan
Afghanistan - Mobile - Salaam

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]93[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]9375[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]9370[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9371[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]9378[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9376[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]93765[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]93766[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]93767[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]9377[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]9372[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]93744[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]93747[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]9379[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]9374[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=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),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]