Lookup country from list of numbers

Hello all

I am new to the forum as a poster, but have read it many times!

I am trying to write a small excel based tool.

Basically I have a long list of phone numbers.

They are from all different countries. They start with the international dial code of each country.

Even within the same country, they can be different total lengths.

I also have a list of what country corresponds to what international dial code.

Sometimes the list shows one number, like US and Canada which is 1. Sometimes the list shows two numbers like 44 for UK. Sometimes for places near the US they use 1767 for Dominica for example.

But it appears the longest international dial code is four numbers like Dominica 1767 and the shortest is the US and Canada with 1.

What I would like to do it write a tool that tells me what country each number comes from, but it must start with the longest dial codes first like Dominica 1767 so it knows Dominica is 1767 (if you get what i mean!).

So if X is anything, 44XXXXXXXXX is a UK number. 1767XXXXXXX is Dominica 1XXXXXXXXXX is US as long as its not one of those other countries that starts 1.

In the longer term I would like to put a larger database of numbers in it so when I put a list of numbers in it, it spits out a better answer like 447590776132 is UK mobile number issued by o2 because 44 is UK, 7590 is o2.

So, basically it needs to look at the first four numbers and match the best one first.

I would be so grateful for your help!

Hello Ace

Thanks for your prompt reply.

I think I sort of get what your saying. It checks for 4 numbers first, then 3 then 2 then 1? The left just shows the 4 number to left, then 3 then 2 then 1?

But I get #VALUE!

Don't I have to specify how many countries I have?

I have B1 to B234 as country dial codes with B1 as 93 (for Afghanistan) and B234 as 263 (for Zimbabwe)

And I have C1 as Afghanistan and C234 for Zimbabwe.

So don't I have to specify B1 to B234 somewhere and C1 to C234 somewhere? Or don't I get it?!

I have numbers going from A1 to A2708.

It worked!!!!

Thats fantastic!!!!

Thanks so much!

Hello all

I am using this and it works great:

=INDEX(W:W,IFERROR(MATCH(LEFT(A2,4)*1,V:V,0),IFERROR(MATCH(LEFT(A2,3)*1,V:V,0),IFERROR(MATCH(LEFT(A2,2)*1,V:V,0),IFERROR(MATCH(LEFT(A2,1)*1,V:V,0),"")))))

But to tidy things up I would like to use this and reference another sheet called Country

In the country sheet A2 says 93 and B2 says Afghanistan

and it goes down to A239 1809 and B239 Domincan Republic 2

I initally tried

=INDEX(Country!B2:Country!B2,IFERROR(MATCH(LEFT(A2,4)*1,V:V,0),IFERROR(MATCH(LEFT(A2,3)*1,V:V,0),IFERROR(MATCH(LEFT(A2,2)*1,V:V,0),IFERROR(MATCH(LEFT(A2,1)*1,V:V,0),"")))))

leaving the country code number intact on the existing sheet, and just trying to pull the country data from the other sheet.

But I get #REF!

If i just try a cell by it self and put =Country!B2 in it I get Afghanistan, which is what I expect.

Can someone tell me how I am screwing up?

Many thanks again

Phil

Hi

I've tried to do this but it says file size is too high, even when I chop loads of data out.

=INDEX(W:W,IFERROR(MATCH(LEFT(A2,4)*1,V:V,0),IFERROR(MATCH(LEFT(A2,3)*1,V:V,0),IFERROR(MATCH(LEFT(A2,2)*1,V:V,0),IFERROR(MATCH(LEFT(A2,1)*1,V:V,0),"")))))

if above works i tried

=INDEX(Country!B:Country!B,IFERROR(MATCH(LEFT(A2,4)*1,V:V,0),IFERROR(MATCH(LEFT(A2,3)*1,V:V,0),IFERROR(MATCH(LEFT(A2,2)*1,V:V,0),IFERROR(MATCH(LEFT(A2,1)*1,V:V,0),"")))))
still doesn't work

then i read somewhere about '

and i tried this

=INDEX('Country'!B:'Country'!B,IFERROR(MATCH(LEFT(A2,4)*1,V:V,0),IFERROR(MATCH(LEFT(A2,3)*1,V:V,0),IFERROR(MATCH(LEFT(A2,2)*1,V:V,0),IFERROR(MATCH(LEFT(A2,1)*1,V:V,0),"")))))
but that doesn't work either

and this doesn't work

=INDEX('Country!B2':'Country!B2',IFERROR(MATCH(LEFT(A2,4)*1,V:V,0),IFERROR(MATCH(LEFT(A2,3)*1,V:V,0),IFERROR(MATCH(LEFT(A2,2)*1,V:V,0),IFERROR(MATCH(LEFT(A2,1)*1,V:V,0),"")))))

Hello all

Since you guys helped me above, my excel skills have got much better! Thanks

I've got this database that is 954692 rows that I have managed to cut down just enough so excel can open it!

I am using this:

=INDEX(K:K,IFERROR(MATCH(LEFT(AB2,16)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,15)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,14)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,13)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,12)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,11)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,10)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,9)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,8)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,7)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,6)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,5)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,4)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,3)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,2)*1,Y:Y,0),IFERROR(MATCH(LEFT(AB2,1)*1,Y:Y,0),"")))))))))))))))))

its a number matching thing that matches the most accurate number range first, then progressively less accurate.

I get the #VALUE! error and I am pretty sure its because some of the K column either has invalid characters in it, or has gaps, or something else excel does not like.

The K column is a list of world wide telecom companies and has lots of . and characters with ump Lars and goodness knows what else. But as I say there are 954692 rows! Is there an easy way I can make this column excel formula compliant without ruining the 3rd party supplied data too much?