Changing from Nested IF statement to VLOOKUP, however cannot find an answer....Please help!

[hbap08]

Hi All,
first post, however this has been a long time reference site and has answered many of my previous issues.

I have an issue that I cannot seem to resolve which I am wondering if someone can help.
I have exported a large list of phone numbers (~15,000) and I am trying to reformat it to be a correct +E.164 phone number based on a partial match within the number (typically the area code and local prefix)

I managed to this working based on a partial match using IF(ISNUMBER(SEARCH method. I then concatenate using the last 4 numbers (Right) which is typically their extension number to provide a correctly reformatted +E.164 number. Unfortunately I can only have 64 levels of nesting and I need ~100, as that is how many sites we have and I need to query.

For example, if the following user have entered a phone number in a list ;

A B C
1 Name Phone Number Reformatted Number
2 Bob Smith 123.456.7890 +1.123.456.7890
3 Mary Jane 22.333.8855 +1.722.333.8855
4 Pete Collins +1.0.845.554.2222 +1.845.554.2222

An example of the formula I am using is (only showing 3 nested statements which match the above 3 examples);
=
IF(ISNUMBER(SEARCH("23456",E2))+N("Site-1"),CONCATENATE("+1.123.456.",RIGHT(E2,4)),
IF(ISNUMBER(SEARCH("22233",E2))+N("Site-2"),CONCATENATE("+1.722.233.",RIGHT(E2,4)),
IF(ISNUMBER(SEARCH("45554",E2))+N("Site-3"),CONCATENATE("+1.645.554.",RIGHT(E2,4)),
False)

This all works fine, however this is only an example of 3 sites. The formula currently consists of 64 nested IF statements and I need more. I read that a lot of this can be fixed by using a VLOOKUP, however I can only get VLOOKUP to do an exact match, (ie, if I was looking for "23456", then the user would have to have "23456" as their number to get a match. I cannot seem to figure out how to find "23456" in "1234567890" to then return "+1.123.456.7890"
I have looked at wildcards etc., however I couldn't get this working

I have now created a VLOOKUP table as follows in "Sheet 2".
The table has a list (~100 sites) of numbers to search for and also correctly formatted +E.164 numbers (without the extension numbers, as they would be CONCATENATED using the last 4 numbers (RIGHT)) as I had in the IF statements

A B C
1 Match Site Reformatted
2 23456 Site 1 +1.123.456.
3 22233 Site 2 +1.722.233.
4 45554 Site 3 +1.645.554.

Hope this makes sense?

Is anyone aware of a way that I could use a VLOOKUP for an exact match within a larger number so I can get away from the 64 array limitation?

Any information would be appreciated.

Kind Regards,

Simon

 

[mole999]

I cannot seem to figure out how to find "23456" in "1234567890" to then return "+1.123.456.7890"

=Mid(cellvalue,2,5) basically this should be looking for the portion, 2 in and 5 wide, does that help

using ,0) at the end of vlookup is for an exact match ,1) would give nearest above as its supposed to be sorted in order

 

[hbap08]

Hi Mole999,

Thanks for your feedback. I have resorted back to using =LEFT(RIGHT(cellvalue,10),6), as it returns the last 10 digits from the right, minus the last 4. [123456]
In another cell I return the last 4 digits =RIGHT(cellvalue,4) [7890] ,then preform a VLOOKUP on the PREFIX [123456] and associated +E.164 value [+1.123.456.]
This then concatenates the +E.164 value with the 4 digit extension to return the final result [+1.123.456.7890]
It is a little convoluted, however it is working.... just not as nicely as the IF statement below, as it only requires one forumla in one column and doesnt care how many digits there are in the number...its just a pity that I cannot have 200 IF statements!;

IF(ISNUMBER(SEARCH("123456",cellvalue))+N("Site-1"),CONCATENATE("+1.123.456.",RIGHT(cellvalue,4)),FALSE

Thanks again