Help to calculate prize

Hi guys

I was wondering if someone could help me with this challenage.
I am looking for a formula to calculate a prize based on the prefix (telephonenumber breakout).
These breakouts are to call international.

See below the prefix and rate example template.
It works like this, if the prefix is mentioned al the digits after the prefix would be ignored and the rate for the mentioned prefix would be charged.
So someone is dialing 932523658923 the rate of 0.1808 would be charged but if someone is calling 937352685984 the rate of 0.1466 woud be charged beceause it prefix is mentioned in the system.

I dont have the prize for all prefixes, so i need to match the prize based on the existing prize.

So in below example they are sorted first on name and then on prefix. (93)
Some of the prefixes can go up to 12 digits.

Work sheet

Afghanistan 93​ 0.1808​
Afghanistan 935​
Afghanistan Mobile 937​ 0.1691​
Afghanistan Mobile 9373​ 0.1466​
Afghanistan Mobile AT 9375​
Afghanistan Mobile AWCC 9370​ 0.1663​
Afghanistan Mobile AWCC 93702​
Afghanistan 933​

Result after formula
935 should be 0.1808
9375 should be 0.1691
93702 would 0.1663
933 would be 0.1808

Hope somebody could help me

I agree with Pepe, but think the word prize should be rate or price.


Also it is easy to search the start of a number to look up the price, but the problem is you have varying length strings, which could be accommodated in a nested if statement, however if your real question involves more than the 7 options that you have now, this would prove to be problematic.

Is every string you match 3 4 or 5 characters?

if so iferror(vlookup((left,cell,5),range of characters and tarrifs,2,false),iferror(vlookup((left,cell,4),range of characters and tarrifs,2,false),vlookup((left,cell,3),range of characters and tarrifs,2,false)))


may be the solution you are looking for you return the rate with a vlookup matching 5 characters, if this fails, you try with 4 if this fails you try with 3. Lots of internet pages on how vlookup works
https://exceljet.net/excel-functions...ookup-function

Hi the formula is not working,

I have attached a sample excel sheet where we can put in the formula, can you help ?