okiedokie2017
New Member
- Joined
- Sep 14, 2017
- Messages
- 14
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
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