LOOKUP(1E+100, SEARCH.... issue

[okiedokie2017]

Hi,

Im having a problem with the spreadsheet I am working on. I thought everything is working perfectly, until I noticed some issues where the formula is not pulling the correct data I was looking for.

So I have a database with a column "Destination prefix" and another column for list price.
I have a calculator where you can put any phone number (with the correct prefix) and the formula will gives you the prefix for the phone number, and hence you can vlookup the prefix to get the list price.

My database has around 50,000 rows, but I found out that the formula is not working well with any prefix starts with "1". If I have the prefix of Dominician Republic with the prefix of "1829757", the formula will only recognize the prefix of "1", which is US prefix.

I attached a sample excel to make it clearer.

The formula I used is:
=IFERROR(LOOKUP(1E+100,SEARCH($C$2:$C$48963,LEFT(G5,$C$2:$C$48963)),$C$2:$C$48963),"")

When the formula is used on a small database, everything works. But when I used on my spreadsheet, it formula is mixing up the prefixes that start with 1 again.

Any suggestion would be much appreciated

 

[Aladin Akyurek]

Care to post some examples including ones for which the formula does not work?

 

[okiedokie2017]

Oops.

My formula is:
=IFERROR(LOOKUP(1E+100,SEARCH($C$2:$C$48963,LEFT(G5,LEN($C$2:$C$48963))),$C$2:$C$48963),"")

 

[okiedokie2017]

Hello,
I was trying to post some pictures or excel files but cant find the option to upload them to here.

 

[Aladin Akyurek]

Hello,
I was trying to post some pictures or excel files but cant find the option to upload them to here.

Pictures are not useful, unless one retypes into Excel what it shows.

For possible methods for posting useable samples, see Attachments.