Evaluate one cell against an array

[tatoon]

I have two sets of information, on one hand I have telephone numbers and in the other set I have prefixes and countries. My goal is to tell to which country each number belongs:

i.e.


Numbers Prefix Country
4476324125 44 UK
3354326544 33 France
9713425432 971 UAE
9143253245 91 India
1343543253 1 USA
4432412412
4924241214
7431242424



So I would need to add a column next to "Numbers" saying to which country each number belongs.

My list has a few thousand numbers and a couple of hundred prefixes.
I tried with some array formula, but cannot make it work. Any idea on how this can be done? It would be interesting not to use VB.

Thanx!

[martindwilson]

try this i used a few helper columns then a lookup to avoid long nested if's

[DonkeyOte]

Unless your prefixes are all mutually exclusive (unlikely) you will need to add some additional logic to your criteria...

To explain - USA is not the only dialling code beginning with 1 - eg Puerto Rico 1787 ... now although this may be a bad example I'm merely trying to illustrate how you will need to use logic in addition to the dialling code before you can try to assign numbers to codes.

You could for ex. use length of number as an additional identifier.
So adjacent to USA you may enter 10, ie USA numbers should be 10 digits in length inclusive of dialling code. Puerto Rico number length is likely to be different - ie 4 (code) + number

Obviously if the number is 6 digits then you still have a problem, and some countries may have numbers of differing lengths (pending regional code etc...) so there are still obvious problems even utilising the above but this process will still to some extent lessen the risk of categorising numbers in error.

[DonkeyOte]

Martin, FWIW, you could mirror your results without helpers using something like

B1:
=INDEX($I$1:$I$238,MATCH(TRUE,INDEX(($H$1:$H$238=LEFT($A1,LEN($H$1:$H$238))),0),0))

[Palmetto]

Edit: removed suggested formula and workbook. The solution is not viable based on details in subsequent posts

[martindwilson]

yep thats a bit better(well quite alot really) donkeyote
so heres v2 i moved duplicates to same row as well
hm puerto rico i have as just 787 when dialed from europe not 1787 its only 1787 when called from usa/canada!

[DonkeyOte]

Puerto Rico international code aside the general problems persists even with UK based codes ... eg:

345* - Caymans or Spanish ?
441* - Bermudan or UK ?

etc I'm not sure with the info. provided a solution is viable.

[martindwilson]

ok try version 3!

[DonkeyOte]

We will have to agree to disagree Martin... I don't think given the info we have this is a viable task.

[martindwilson]

correction i see what you mean
manchester is say +44161
so digit string length and or 4th digit must come into play somehow
now ive hade a better look bermuda from switzerland is +1441 not +441 so it depends now whether the numbers given show the full country code froma given location. then a lookup table should be able to pick them out

[tatoon]

Great!!

It works, actually I took the version 2, everything in one column/formula.

To avoid the problem you mention afterwards, I sort the prefixes in a descending order, so the first prefix that the formula will find will be the most exclusive.

[tatoon]

Actually adding a LARGE in the v2 formula gets the same results as in v3, but all in just one formula, but it makes the calculations too heavy for the CPU, it's not practical with my computer. Much faster sorting the numbers and using the v2 formula.