Matching phone numbers formula

T

Tripleseas

Board Regular
Joined
Jul 12, 2022
Messages
87
Office Version
  1. 2013
Platform
  1. Windows
Hello community,
I have one data set that contains different phone numbers as shown below. ( Phones dataset column ). I added a digit number and as you can see the majority of these phone's numbers are 11 digits. these info are pulled from an ERP.

the second data set is the matching process. ( Phone 2 column is typed manually by different people ). my goal is to match whether these phone numbers exist in the first data set ( phones dataset column). so i added a vlookup and its working. the phones numbers that do not exist in the first dataset are shown. but the manually typed phone numbers are not in the same format as the numbers pulled from the ERP. So i want to know what is the best solution to go about this ? as this is a task that will be repeated many times and I need to figure out a way to automate this the best safe way so i can avoid errors.

i'm afraid that some phone numbers that are typed manually might exist on the data set but it's just the format that's not matching and my vlookup formula cant detect it. if you can understand me.

thank you guys for the much-needed help.




Matching.xlsx
ABCDEF
2The look up table The matching process
3Phones datasetdigit numberPhones 2Matching resultdigit number
43369850072211346420803493464208034911
53375371877311337644804843376448048411
63460026217511336514778363365147783611
73365116545111346436504873464365048711
83363526050311337815025803378150258011
93464170433111393492960583934929605811
10337739502021134635273041not found9
11336427077691134617000986not found9
12336394299891134617009408not found9
13335698529851134657382545not found9
143364469156511336738938193367389381911
15337760660231134600297866not found9
16337731428131132486040820not found9
173363296508811602097390not found9
183374990903511722317741not found9
194366618597311632287993not found9
203362997676311628468524not found9
213464208034911671306030not found9
223472231774111667894092not found9
233362825035611766682220not found9
243460209739011631458918not found9
253463161682011663486304not found9
2639320966160812603523690not found9
273463228799311666125064not found9
283462849852411769284167not found9
293248749400411493306400not found9
303376448048411485924451not found9
31346713060301133634567654not found9
323375894446711631507942not found9
333246569614311324874940043248749400411
343464135688711641356887not found9
3533651477836113934826512643.93483E+1112
3639348265126412330626639509not found9
373466789409211661186664not found9
383171134481111632298592not found9
393374839159511602328304not found9
403362158956711212670837277not found9
4139320966252412722422864not found9
423362663950911631567158not found9
433376968818511330744535040not found9
443464365048711346020035003460200350011
45346314589181133618828223not found9
463378150258011346313579263463135792611
473388744222511346316898243463168982411
48336859858551124631066240not found9
493460352369011612475930not found9
503463229859211324651165083246511650811
51336144171831133763311748not found9
52346332166721131629514030not found9
533460232830411346313581883463135818811
543376928416711324891177383248911773811
553367083727711632646811not found9
563364982886111642549857not found9
573934929605811641452785not found9
583367591667711330616220345not found9
593466669186211631492427not found9
603466112210411631009472not found9
613472242286411600810420not found9
623463156715811641593115not found9
633362184452311324869440863248694408611
643365230100611212655088941not found9
653368746543011324862085763248620857611
663463428673311622294409not found9
673463417740611602136024not found9
683463267242011664046031not found9
693366838450411491788232630not found9
703468820118811393246188862not found9
713364176040311395557234588not found9
723460200350011632952987not found9
7334631911134113369713301not found9
743363443415511330625230548not found9
7534676096453113307580527not found9
763360392665411693378822not found9
773360530648811337662024673376620246711
78491578831533813631177138not found9
793463214637711645096065not found9
803377739337011602192976not found9
813363974958611324677478773246774787711
823360582542011346325066013463250660111
833248446984511324893037413248930374111
84316384331661134697815913not found9
853463135792611324920706003249207060011
863463261423011346669258943466692589411
8734632249923113466291856not found9
883464089544611324848791053248487910511
893461254378811336080310243360803102411
903249420687511336011101973360111019711
913362801449011346125124233461251242311
923467489718411336501689743365016897411
93336171524171133654841200not found9
Feuil2
Cell Formulas
RangeFormula
E4:E93E4=IFERROR(VLOOKUP(D4,$A$4:$A$539,1,0),"not found")
B4:B93,F4:F93F4=LEN(E4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:D242,D267:D1048576Cell ValueduplicatestextNO
 
Sort by date Sort by votes
Sufiyan97 said:
You're welcome.
Glad to help
Click to expand...
Thank you .
I ran to a scenario that's really repetetive in the data set sent to us :
Some phones that we want to match with the phones in the lookup table are like this :

330626639509 ( they add a 0 after the country code , in this case it's 33 ) ( because it's manually typed by people )

the phone in the lookup table are like this : 33626639509 ( they dont include 0 after country code ) ( pulled from a system )

so the formula didn't match theses cases until i manually deleted the zero then the formula matched it. is there anyway to include this? i'm working on large data set and i can't do this manually. thank you and sorry for coming back. i know this is mind consuming :)
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try

Book2
ABCDEF
1
2The look up table The matching process
3Phones datasetdigit numberPhones 2Matching resultdigit number
43369850072211346420803493464208034911
53375371877311337644804843376448048411
63460026217511336514778363365147783611
73365116545111346436504873464365048711
83363526050311337815025803378150258011
93464170433111393492960583934929605811
10337739502021134635273041Not Found9
11336427077691134617000986Not Found9
12336394299891134617009408Not Found9
13335698529851134657382545Not Found9
14336446915651133673893819Not Found9
15337760660231134600297866Not Found9
16337731428131132486040820Not Found9
1733632965088116020973906020973909
1833749909035117223177417223177419
1943666185973116322879936322879939
203362997676311628468524Not Found9
2134642080349116713060306713060309
2234722317741116678940926678940929
233362825035611766682220Not Found9
2434602097390116314589186314589189
253463161682011663486304Not Found9
263.9321E+11126035236906035236909
273463228799311666125064Not Found9
2834628498524117692841677692841679
293248749400411493306400Not Found9
303376448048411485924451Not Found9
31346713060301133634567654Not Found9
323375894446711631507942Not Found9
333246569614311324874940043248749400411
3434641356887116413568876413568879
3533651477836113.93483E+113.93483E+1112
363.93483E+11123.30627E+113362663950911
373466789409211661186664Not Found9
3831711344811116322985926322985929
3933748391595116023283046023283049
4033621589567112.12671E+11Not Found9
413.9321E+11127224228647224228649
4233626639509116315671586315671589
4333769688185113.30745E+11Not Found9
443464365048711346020035003460200350011
45346314589181133618828223Not Found9
463378150258011346313579263463135792611
47338874422251134631689824Not Found9
48336859858551124631066240Not Found9
493460352369011612475930Not Found9
50346322985921132465116508Not Found9
51336144171831133763311748Not Found9
52346332166721131629514030Not Found9
53346023283041134631358188Not Found9
54337692841671132489117738Not Found9
553367083727711632646811Not Found9
563364982886111642549857Not Found9
573934929605811641452785Not Found9
5833675916677113.30616E+11Not Found9
593466669186211631492427Not Found9
603466112210411631009472Not Found9
613472242286411600810420Not Found9
623463156715811641593115Not Found9
63336218445231132486944086Not Found9
6433652301006112.12655E+11Not Found9
65336874654301132486208576Not Found9
663463428673311622294409Not Found9
673463417740611602136024Not Found9
683463267242011664046031Not Found9
6933668384504114.91788E+11Not Found9
7034688201188113.93246E+11Not Found9
7133641760403113.95557E+11Not Found9
723460200350011632952987Not Found9
7334631911134113369713301Not Found9
7433634434155113.30625E+11Not Found9
7534676096453113307580527Not Found9
763360392665411693378822Not Found9
77336053064881133766202467Not Found9
784.91579E+1213631177138Not Found9
793463214637711645096065Not Found9
803377739337011602192976Not Found9
81336397495861132467747877Not Found9
82336058254201134632506601Not Found9
83324844698451132489303741Not Found9
84316384331661134697815913Not Found9
85346313579261132492070600Not Found9
86346326142301134666925894Not Found9
8734632249923113466291856Not Found9
88346408954461132484879105Not Found9
89346125437881133608031024Not Found9
90324942068751133601110197Not Found9
91336280144901134612512423Not Found9
92346748971841133650168974Not Found9
93336171524171133654841200Not Found9
Sheet1
Cell Formulas
RangeFormula
E4:E93E4=IF(LEN(D4)<11,IFERROR(VLOOKUP("*"&D4,RIGHT($A$4:$B$93,LEN(D4)),1,0),IFERROR(VLOOKUP(LEFT(D4,2)&RIGHT(D4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(D4)-3),1,0),"Not Found")),IFERROR(VLOOKUP(D4,$A$4:$A$539,1,0),IFERROR(VLOOKUP(LEFT(D4,2)&RIGHT(D4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(D4)-3),1,0),"Not Found")))
F4:F93,B4:B93F4=LEN(E4)
 
Upvote 0
Sufiyan97 said:
Try

Book2
ABCDEF
1
2The look up table The matching process
3Phones datasetdigit numberPhones 2Matching resultdigit number
43369850072211346420803493464208034911
53375371877311337644804843376448048411
63460026217511336514778363365147783611
73365116545111346436504873464365048711
83363526050311337815025803378150258011
93464170433111393492960583934929605811
10337739502021134635273041Not Found9
11336427077691134617000986Not Found9
12336394299891134617009408Not Found9
13335698529851134657382545Not Found9
14336446915651133673893819Not Found9
15337760660231134600297866Not Found9
16337731428131132486040820Not Found9
1733632965088116020973906020973909
1833749909035117223177417223177419
1943666185973116322879936322879939
203362997676311628468524Not Found9
2134642080349116713060306713060309
2234722317741116678940926678940929
233362825035611766682220Not Found9
2434602097390116314589186314589189
253463161682011663486304Not Found9
263.9321E+11126035236906035236909
273463228799311666125064Not Found9
2834628498524117692841677692841679
293248749400411493306400Not Found9
303376448048411485924451Not Found9
31346713060301133634567654Not Found9
323375894446711631507942Not Found9
333246569614311324874940043248749400411
3434641356887116413568876413568879
3533651477836113.93483E+113.93483E+1112
363.93483E+11123.30627E+113362663950911
373466789409211661186664Not Found9
3831711344811116322985926322985929
3933748391595116023283046023283049
4033621589567112.12671E+11Not Found9
413.9321E+11127224228647224228649
4233626639509116315671586315671589
4333769688185113.30745E+11Not Found9
443464365048711346020035003460200350011
45346314589181133618828223Not Found9
463378150258011346313579263463135792611
47338874422251134631689824Not Found9
48336859858551124631066240Not Found9
493460352369011612475930Not Found9
50346322985921132465116508Not Found9
51336144171831133763311748Not Found9
52346332166721131629514030Not Found9
53346023283041134631358188Not Found9
54337692841671132489117738Not Found9
553367083727711632646811Not Found9
563364982886111642549857Not Found9
573934929605811641452785Not Found9
5833675916677113.30616E+11Not Found9
593466669186211631492427Not Found9
603466112210411631009472Not Found9
613472242286411600810420Not Found9
623463156715811641593115Not Found9
63336218445231132486944086Not Found9
6433652301006112.12655E+11Not Found9
65336874654301132486208576Not Found9
663463428673311622294409Not Found9
673463417740611602136024Not Found9
683463267242011664046031Not Found9
6933668384504114.91788E+11Not Found9
7034688201188113.93246E+11Not Found9
7133641760403113.95557E+11Not Found9
723460200350011632952987Not Found9
7334631911134113369713301Not Found9
7433634434155113.30625E+11Not Found9
7534676096453113307580527Not Found9
763360392665411693378822Not Found9
77336053064881133766202467Not Found9
784.91579E+1213631177138Not Found9
793463214637711645096065Not Found9
803377739337011602192976Not Found9
81336397495861132467747877Not Found9
82336058254201134632506601Not Found9
83324844698451132489303741Not Found9
84316384331661134697815913Not Found9
85346313579261132492070600Not Found9
86346326142301134666925894Not Found9
8734632249923113466291856Not Found9
88346408954461132484879105Not Found9
89346125437881133608031024Not Found9
90324942068751133601110197Not Found9
91336280144901134612512423Not Found9
92346748971841133650168974Not Found9
93336171524171133654841200Not Found9
Sheet1
Cell Formulas
RangeFormula
E4:E93E4=IF(LEN(D4)<11,IFERROR(VLOOKUP("*"&D4,RIGHT($A$4:$B$93,LEN(D4)),1,0),IFERROR(VLOOKUP(LEFT(D4,2)&RIGHT(D4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(D4)-3),1,0),"Not Found")),IFERROR(VLOOKUP(D4,$A$4:$A$539,1,0),IFERROR(VLOOKUP(LEFT(D4,2)&RIGHT(D4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(D4)-3),1,0),"Not Found")))
F4:F93,B4:B93F4=LEN(E4)
Click to expand...
It worked great. i executed it as an array formula.

Thank you so much for the help again. :)
 
Upvote 0
Sufiyan97 said:
You're welcome.
Click to expand...

Mr Sufiyan, I swear this is the last one :) regarding a new tweak.

the lookup table contain an added column " type" , i want to note this information in my matching process is that possible as below :

thank you and really I'm embarrassed to come back this is the last one. next time i will start a new post. hope i'm not bothering you. thanks

the formula result is not showing i guess because i copied it in an excel sheet with french language.
Matching.xlsx
ABCDEFGH
2The look up table The matching process
3Phones datasetdigit numberTypePhones 2Matching resultdigit numberNoting type
433698500722#NOM?rejected34642080349#NOM?#NOM?Verified
533753718773#NOM?rejected33764480484#NOM?#NOM?
634600262175#NOM?rejected33651477836#NOM?#NOM?
733651165451#NOM?rejected34643650487#NOM?#NOM?
833635260503#NOM?rejected33781502580#NOM?#NOM?
934641704331#NOM?rejected39349296058#NOM?#NOM?
1033773950202#NOM?rejected34635273041#NOM?#NOM?
1133642707769#NOM?rejected34617000986#NOM?#NOM?
1233639429989#NOM?rejected34617009408#NOM?#NOM?
1333569852985#NOM?rejected34657382545#NOM?#NOM?
1433644691565#NOM?rejected33673893819#NOM?#NOM?
1533776066023#NOM?rejected34600297866#NOM?#NOM?
1633773142813#NOM?rejected32486040820#NOM?#NOM?
1733632965088#NOM?rejected602097390#NOM?#NOM?
1833749909035#NOM?rejected722317741#NOM?#NOM?
1943666185973#NOM?Verified632287993#NOM?#NOM?
2033629976763#NOM?Verified628468524#NOM?#NOM?
2134642080349#NOM?Verified671306030#NOM?#NOM?
2234722317741#NOM?Verified667894092#NOM?#NOM?
2333628250356#NOM?Verified766682220#NOM?#NOM?
2434602097390#NOM?Verified631458918#NOM?#NOM?
2534631616820#NOM?Verified663486304#NOM?#NOM?
263.9321E+11#NOM?Verified603523690#NOM?#NOM?
2734632287993#NOM?Verified666125064#NOM?#NOM?
2834628498524#NOM?Verified769284167#NOM?#NOM?
2932487494004#NOM?Verified493306400#NOM?#NOM?
3033764480484#NOM?Verified485924451#NOM?#NOM?
3134671306030#NOM?Verified33634567654#NOM?#NOM?
3233758944467#NOM?In progress631507942#NOM?#NOM?
3332465696143#NOM?In progress32487494004#NOM?#NOM?
3434641356887#NOM?In progress641356887#NOM?#NOM?
3533651477836#NOM?In progress3.93483E+11#NOM?#NOM?
363.93483E+11#NOM?In progress3.30627E+11#NOM?#NOM?
3734667894092#NOM?In progress661186664#NOM?#NOM?
3831711344811#NOM?In progress632298592#NOM?#NOM?
3933748391595#NOM?In progress602328304#NOM?#NOM?
4033621589567#NOM?In progress2.12671E+11#NOM?#NOM?
413.9321E+11#NOM?In progress722422864#NOM?#NOM?
4233626639509#NOM?In progress631567158#NOM?#NOM?
4333769688185#NOM?In progress3.30745E+11#NOM?#NOM?
4434643650487#NOM?In progress34602003500#NOM?#NOM?
4534631458918#NOM?In progress33618828223#NOM?#NOM?
4633781502580#NOM?In progress34631357926#NOM?#NOM?
4733887442225#NOM?In progress34631689824#NOM?#NOM?
4833685985855#NOM?In progress24631066240#NOM?#NOM?
4934603523690#NOM?In progress612475930#NOM?#NOM?
5034632298592#NOM?In progress32465116508#NOM?#NOM?
5133614417183#NOM?In progress33763311748#NOM?#NOM?
5234633216672#NOM?In progress31629514030#NOM?#NOM?
5334602328304#NOM?In progress34631358188#NOM?#NOM?
5433769284167#NOM?In progress32489117738#NOM?#NOM?
5533670837277#NOM?In progress632646811#NOM?#NOM?
5633649828861#NOM?In progress642549857#NOM?#NOM?
5739349296058#NOM?In progress641452785#NOM?#NOM?
5833675916677#NOM?In progress3.30616E+11#NOM?#NOM?
5934666691862#NOM?In progress631492427#NOM?#NOM?
6034661122104#NOM?In progress631009472#NOM?#NOM?
6134722422864#NOM?In progress600810420#NOM?#NOM?
6234631567158#NOM?In progress641593115#NOM?#NOM?
6333621844523#NOM?In progress32486944086#NOM?#NOM?
6433652301006#NOM?In progress2.12655E+11#NOM?#NOM?
6533687465430#NOM?In progress32486208576#NOM?#NOM?
6634634286733#NOM?In progress622294409#NOM?#NOM?
6734634177406#NOM?In progress602136024#NOM?#NOM?
6834632672420#NOM?In progress664046031#NOM?#NOM?
6933668384504#NOM?In progress4.91788E+11#NOM?#NOM?
7034688201188#NOM?In progress3.93246E+11#NOM?#NOM?
7133641760403#NOM?In progress3.95557E+11#NOM?#NOM?
7234602003500#NOM?In progress632952987#NOM?#NOM?
7334631911134#NOM?In progress3369713301#NOM?#NOM?
7433634434155#NOM?In progress3.30625E+11#NOM?#NOM?
7534676096453#NOM?In progress3307580527#NOM?#NOM?
7633603926654#NOM?In progress693378822#NOM?#NOM?
7733605306488#NOM?In progress33766202467#NOM?#NOM?
784.91579E+12#NOM?In progress631177138#NOM?#NOM?
7934632146377#NOM?In progress645096065#NOM?#NOM?
8033777393370#NOM?In progress602192976#NOM?#NOM?
8133639749586#NOM?In progress32467747877#NOM?#NOM?
8233605825420#NOM?In progress34632506601#NOM?#NOM?
8332484469845#NOM?In progress32489303741#NOM?#NOM?
8431638433166#NOM?In progress34697815913#NOM?#NOM?
8534631357926#NOM?In progress32492070600#NOM?#NOM?
8634632614230#NOM?In progress34666925894#NOM?#NOM?
8734632249923#NOM?In progress3466291856#NOM?#NOM?
8834640895446#NOM?In progress32484879105#NOM?#NOM?
8934612543788#NOM?In progress33608031024#NOM?#NOM?
9032494206875#NOM?In progress33601110197#NOM?#NOM?
9133628014490#NOM?In progress34612512423#NOM?#NOM?
9234674897184#NOM?In progress33650168974#NOM?#NOM?
9333617152417#NOM?In progress33654841200#NOM?#NOM?
Feuil4
Cell Formulas
RangeFormula
F4:F93F4=IF(LEN(E4)<11,IFERROR(VLOOKUP("*"&E4,RIGHT($A$4:$B$93,LEN(E4)),1,0),IFERROR(VLOOKUP(LEFT(E4,2)&RIGHT(E4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(E4)-3),1,0),"Not Found")),IFERROR(VLOOKUP(E4,$A$4:$A$539,1,0),IFERROR(VLOOKUP(LEFT(E4,2)&RIGHT(E4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(E4)-3),1,0),"Not Found")))
B4:B93,G4:G93G4=LEN(F4)
 
Upvote 0
Do you want just want type to return incolumn H based on lookup vblue in column E

Book2
ABCDEFGH
1The look up table The matching process
2Phones datasetdigit numberTypePhones 2Matching resultdigit numberNoting type
33369850072211rejected346420803493376448048411Verified
43375371877311rejected337644804843365147783611Verified
53460026217511rejected336514778363464365048711In progress
63365116545111rejected346436504873378150258011In progress
73363526050311rejected33781502580393492960589In progress
83464170433111rejected39349296058Not Found9In progress
93377395020211rejected34635273041Not Found9Not Found
103364270776911rejected34617000986Not Found9Not Found
113363942998911rejected34617009408Not Found9Not Found
123356985298511rejected34657382545Not Found9Not Found
133364469156511rejected33673893819Not Found9Not Found
143377606602311rejected34600297866Not Found9Not Found
153377314281311rejected324860408206020973909Not Found
163363296508811rejected6020973907223177419Not Found
173374990903511rejected7223177416322879939Not Found
184366618597311Verified632287993Not Found9Not Found
193362997676311Verified6284685246713060309Not Found
203464208034911Verified6713060306678940929Not Found
213472231774111Verified667894092Not Found9Not Found
223362825035611Verified7666822206314589189Not Found
233460209739011Verified631458918Not Found9Not Found
243463161682012Verified6634863046035236909Not Found
253.9321E+1111Verified603523690Not Found9Not Found
263463228799311Verified6661250647692841679Not Found
273462849852411Verified769284167Not Found9Not Found
283248749400411Verified493306400Not Found9Not Found
293376448048411Verified485924451Not Found9Not Found
303467130603011Verified33634567654Not Found11Not Found
313375894446711In progress631507942324874940049Not Found
323246569614311In progress3248749400464135688712Verified
333464135688711In progress6413568873.93483E+1111Not Found
343365147783612In progress3.93483E+11336266395099In progress
353.93483E+1111In progress3.30627E+11Not Found9Not Found
363466789409211In progress6611866646322985929Not Found
373171134481111In progress6322985926023283049Not Found
383374839159511In progress602328304Not Found9Not Found
393362158956712In progress2.12671E+117224228649Not Found
403.9321E+1111In progress7224228646315671589Not Found
413362663950911In progress631567158Not Found11Not Found
423376968818511In progress3.30745E+11346020035009Not Found
433464365048711In progress34602003500Not Found11In progress
443463145891811In progress33618828223346313579269Not Found
453378150258011In progress34631357926Not Found9In progress
463388744222511In progress34631689824Not Found9Not Found
473368598585511In progress24631066240Not Found9Not Found
483460352369011In progress612475930Not Found9Not Found
493463229859211In progress32465116508Not Found9Not Found
503361441718311In progress33763311748Not Found9Not Found
513463321667211In progress31629514030Not Found9Not Found
523460232830411In progress34631358188Not Found9Not Found
533376928416711In progress32489117738Not Found9Not Found
543367083727711In progress632646811Not Found9Not Found
553364982886111In progress642549857Not Found9Not Found
563934929605811In progress641452785Not Found9Not Found
573367591667711In progress3.30616E+11Not Found9Not Found
583466669186211In progress631492427Not Found9Not Found
593466112210411In progress631009472Not Found9Not Found
603472242286411In progress600810420Not Found9Not Found
613463156715811In progress641593115Not Found9Not Found
623362184452311In progress32486944086Not Found9Not Found
633365230100611In progress2.12655E+11Not Found9Not Found
643368746543011In progress32486208576Not Found9Not Found
653463428673311In progress622294409Not Found9Not Found
663463417740611In progress602136024Not Found9Not Found
673463267242011In progress664046031Not Found9Not Found
683366838450411In progress4.91788E+11Not Found9Not Found
693468820118811In progress3.93246E+11Not Found9Not Found
703364176040311In progress3.95557E+11Not Found9Not Found
713460200350011In progress632952987Not Found9Not Found
723463191113411In progress3369713301Not Found9Not Found
733363443415511In progress3.30625E+11Not Found9Not Found
743467609645311In progress3307580527Not Found9Not Found
753360392665411In progress693378822Not Found9Not Found
763360530648813In progress33766202467Not Found9Not Found
774.91579E+1211In progress631177138Not Found9Not Found
783463214637711In progress645096065Not Found9Not Found
793377739337011In progress602192976Not Found9Not Found
803363974958611In progress32467747877Not Found9Not Found
813360582542011In progress34632506601Not Found9Not Found
823248446984511In progress32489303741Not Found9Not Found
833163843316611In progress34697815913Not Found9Not Found
843463135792611In progress32492070600Not Found9Not Found
853463261423011In progress34666925894Not Found9Not Found
863463224992311In progress3466291856Not Found9Not Found
873464089544611In progress32484879105Not Found9Not Found
883461254378811In progress33608031024Not Found9Not Found
893249420687511In progress33601110197Not Found9Not Found
903362801449011In progress34612512423Not Found9Not Found
913467489718411In progress33650168974Not Found0Not Found
92336171524170In progress33654841200 0Not Found
Sheet1
Cell Formulas
RangeFormula
F3:F92F3=IF(LEN(E4)<11,IFERROR(VLOOKUP("*"&E4,RIGHT($A$4:$B$93,LEN(E4)),1,0),IFERROR(VLOOKUP(LEFT(E4,2)&RIGHT(E4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(E4)-3),1,0),"Not Found")),IFERROR(VLOOKUP(E4,$A$4:$A$539,1,0),IFERROR(VLOOKUP(LEFT(E4,2)&RIGHT(E4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(E4)-3),1,0),"Not Found")))
G3:G92,B3:B92G3=LEN(F4)
H3:H92H3=IFERROR(VLOOKUP(E3,A:C,3,0),"Not Found")
 
Upvote 0
Sufiyan97 said:
Do you want just want type to return incolumn H based on lookup vblue in column E

Book2
ABCDEFGH
1The look up table The matching process
2Phones datasetdigit numberTypePhones 2Matching resultdigit numberNoting type
33369850072211rejected346420803493376448048411Verified
43375371877311rejected337644804843365147783611Verified
53460026217511rejected336514778363464365048711In progress
63365116545111rejected346436504873378150258011In progress
73363526050311rejected33781502580393492960589In progress
83464170433111rejected39349296058Not Found9In progress
93377395020211rejected34635273041Not Found9Not Found
103364270776911rejected34617000986Not Found9Not Found
113363942998911rejected34617009408Not Found9Not Found
123356985298511rejected34657382545Not Found9Not Found
133364469156511rejected33673893819Not Found9Not Found
143377606602311rejected34600297866Not Found9Not Found
153377314281311rejected324860408206020973909Not Found
163363296508811rejected6020973907223177419Not Found
173374990903511rejected7223177416322879939Not Found
184366618597311Verified632287993Not Found9Not Found
193362997676311Verified6284685246713060309Not Found
203464208034911Verified6713060306678940929Not Found
213472231774111Verified667894092Not Found9Not Found
223362825035611Verified7666822206314589189Not Found
233460209739011Verified631458918Not Found9Not Found
243463161682012Verified6634863046035236909Not Found
253.9321E+1111Verified603523690Not Found9Not Found
263463228799311Verified6661250647692841679Not Found
273462849852411Verified769284167Not Found9Not Found
283248749400411Verified493306400Not Found9Not Found
293376448048411Verified485924451Not Found9Not Found
303467130603011Verified33634567654Not Found11Not Found
313375894446711In progress631507942324874940049Not Found
323246569614311In progress3248749400464135688712Verified
333464135688711In progress6413568873.93483E+1111Not Found
343365147783612In progress3.93483E+11336266395099In progress
353.93483E+1111In progress3.30627E+11Not Found9Not Found
363466789409211In progress6611866646322985929Not Found
373171134481111In progress6322985926023283049Not Found
383374839159511In progress602328304Not Found9Not Found
393362158956712In progress2.12671E+117224228649Not Found
403.9321E+1111In progress7224228646315671589Not Found
413362663950911In progress631567158Not Found11Not Found
423376968818511In progress3.30745E+11346020035009Not Found
433464365048711In progress34602003500Not Found11In progress
443463145891811In progress33618828223346313579269Not Found
453378150258011In progress34631357926Not Found9In progress
463388744222511In progress34631689824Not Found9Not Found
473368598585511In progress24631066240Not Found9Not Found
483460352369011In progress612475930Not Found9Not Found
493463229859211In progress32465116508Not Found9Not Found
503361441718311In progress33763311748Not Found9Not Found
513463321667211In progress31629514030Not Found9Not Found
523460232830411In progress34631358188Not Found9Not Found
533376928416711In progress32489117738Not Found9Not Found
543367083727711In progress632646811Not Found9Not Found
553364982886111In progress642549857Not Found9Not Found
563934929605811In progress641452785Not Found9Not Found
573367591667711In progress3.30616E+11Not Found9Not Found
583466669186211In progress631492427Not Found9Not Found
593466112210411In progress631009472Not Found9Not Found
603472242286411In progress600810420Not Found9Not Found
613463156715811In progress641593115Not Found9Not Found
623362184452311In progress32486944086Not Found9Not Found
633365230100611In progress2.12655E+11Not Found9Not Found
643368746543011In progress32486208576Not Found9Not Found
653463428673311In progress622294409Not Found9Not Found
663463417740611In progress602136024Not Found9Not Found
673463267242011In progress664046031Not Found9Not Found
683366838450411In progress4.91788E+11Not Found9Not Found
693468820118811In progress3.93246E+11Not Found9Not Found
703364176040311In progress3.95557E+11Not Found9Not Found
713460200350011In progress632952987Not Found9Not Found
723463191113411In progress3369713301Not Found9Not Found
733363443415511In progress3.30625E+11Not Found9Not Found
743467609645311In progress3307580527Not Found9Not Found
753360392665411In progress693378822Not Found9Not Found
763360530648813In progress33766202467Not Found9Not Found
774.91579E+1211In progress631177138Not Found9Not Found
783463214637711In progress645096065Not Found9Not Found
793377739337011In progress602192976Not Found9Not Found
803363974958611In progress32467747877Not Found9Not Found
813360582542011In progress34632506601Not Found9Not Found
823248446984511In progress32489303741Not Found9Not Found
833163843316611In progress34697815913Not Found9Not Found
843463135792611In progress32492070600Not Found9Not Found
853463261423011In progress34666925894Not Found9Not Found
863463224992311In progress3466291856Not Found9Not Found
873464089544611In progress32484879105Not Found9Not Found
883461254378811In progress33608031024Not Found9Not Found
893249420687511In progress33601110197Not Found9Not Found
903362801449011In progress34612512423Not Found9Not Found
913467489718411In progress33650168974Not Found0Not Found
92336171524170In progress33654841200 0Not Found
Sheet1
Cell Formulas
RangeFormula
F3:F92F3=IF(LEN(E4)<11,IFERROR(VLOOKUP("*"&E4,RIGHT($A$4:$B$93,LEN(E4)),1,0),IFERROR(VLOOKUP(LEFT(E4,2)&RIGHT(E4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(E4)-3),1,0),"Not Found")),IFERROR(VLOOKUP(E4,$A$4:$A$539,1,0),IFERROR(VLOOKUP(LEFT(E4,2)&RIGHT(E4,9),LEFT($A$4:$B$93,2)&RIGHT($A$4:$B$93,LEN(E4)-3),1,0),"Not Found")))
G3:G92,B3:B92G3=LEN(F4)
H3:H92H3=IFERROR(VLOOKUP(E3,A:C,3,0),"Not Found")
Click to expand...
Hey,
Actually, i want column H to lookup the phones (column E) in the lookup table phones (column A) and when it's matching to return the type in the lookup table.

based on the sames scenarios we did with the first formula. thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Excel VBA, Vlookup to another sheet in same work book
Replies
6
Views
1K
StephenCrump
S
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
290
Jeffrey Mahoney
Jeffrey Mahoney
F
  • Question Question
Match entries in corresponding cells in B,C & D to Column A and Arrange in Columns E,F,G based on best match
Replies
0
Views
529
frank933
F
A
  • Question Question
Index match match not using second set of match criteria
Replies
3
Views
60
Fluff
Fluff
M
  • Solved
VBA to link cell to website regardless of cell content.
2
Replies
18
Views
785
smozgur
smozgur

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top