serch Country name with caller number

[naveen4pundir]

Hi,
Pls help me on this.

I have 2 excel sheets format is below mention. In sheet one i have some number with ISD code and in secound sheet i have country name and Code. now i want to match Country name in sheet one but my porblem is that there are some code are in 1 degit and some code are in 2,3,4,5 degits. If you see in example 220 is belong to Gambia and 224 belong to Guinea so first i want to match all code who is having 1 degit code second who is having 2 degit than 3 degit, 4 degit respectively.

if i'm useing below mention formula it's give me first value from sheet tow
INDEX('Country Code'!C2:C229,MATCH(TRUE,INDEX(ISNUMBER(FIND('Country Code'!$B$2:$B$228,B4)),0),0))


As per Ist number 54911678222709 Formula will check Ist 5491 digit if it is Match than ok else chek 549 if it's match than ok else check 54 and 5.

Sheet One:-
Caller Number
54911678222709
5493426121222
971501364886
9715013648860
2207771650
22430919600
23190000368
23222273217

Sheet two as Country Code:-
Country Code Name
54 Argentina
971 Emiratos Árabes Unidos
220 Gambia
224 Guinea
231 Liberia
232 Sierra Leona

thanks is in advanced.

[ramananhrm]

Please try these options

Assuming the Country code range is B13:B18 & Name is C13:C18

1. =LOOKUP(2^15,SEARCH($B$13:$B$18,A2),$C$13:$C$18)
2. =INDEX($C$13:$C$18,MATCH(TRUE,ISNUMBER(SEARCH($B$13:$B$18,A2)),0))
3. =INDEX($C$13:$C$18,MATCH(1,--(ISNUMBER(SEARCH($B$13:$B$18,A2))),0))
4. =INDEX($C$13:$C$18,SMALL(IF(1*(ISNUMBER(SEARCH($B$13:$B$18,A2))),ROW($B$13:$B$18)-ROW($B$13)+1),1))

[naveen4pundir]

Thanks buddy,

Pls check attach xls file.
there are two sheet one sheet contain all country code and in second sheet if i put caller number in B4 (in text format) and result should be viable in C4.

[ramananhrm]

Hi Naveen,

Please try this function in C4 of Sheet name 'Main'

=LOOKUP(2^15,SEARCH('Country Code'!$B$2:$B$20,B4),'Country Code'!$C$2:$C$20)

[naveen4pundir]

sorry buddy it's showing wrong result,

2207771650 showing as Egypt but actual country is Gambia.
23190000368 showing as HUNGARY but actual country is Liberia.