I was wondering if anyone could help me with a tricky lookup formula?? I posted it a while ago but it was
a bit complicated to explain what I'm trying to acheive, so I've now done some of the hard work first.
Ok I have
an Excel workbook with 2 worksheets, labelled as "codes" and "data"
The "codes" worksheet contains three columns
Column A shows Country names
Column B shows Country dialling codes
Column C shows Cost per minute to the country
Vatican City 39 0.28
Romania 40 0.22
Switzerland 41 0.06
Austria 43 0.06
United Kingdom 44 0.18
Denmark 45 0.06
Sweden 46 0.23
Norway 47 0.06
Morocco 212 0.34
Algeria 213 0.45
Tunisia 216 0.34
Jamaica 1876 0.34
St. Vincent 1784 0.55
Trinidad 1868 0.65
PLEASE NOTE: There are alot more than this, I am just using this as an
example. Also, note that some area codes are 2-digits, some are 3-digits, and some are 4-digits.
Ok, I also have a
worksheet called "data" and this worksheet is the worksheet which tells us The telephone numbers of the calls we've made. The
telephone numbers are found in column D.
Column F contains the number of minutes the call lasted for.
So leading up to my question (finally!).....
We would like to calculate with a formula, the cost of each of the
calls made. So essentially, the formula would have to do the following:
1) Lookup column D of the "data" worksheet
to find out the telephone number.
2) Match against column B of the "codes" worksheet, to determine the country code and what country the call was made to.
3) Once we know what country we called, we can now check the adjacent cell in column C to tell us the cost per minute of the
4) Now we have the cost per minute of the call, we can then multiply this by the value in column D of "data" to get the cost
of the call.
5) The final value then goes in adjacent column G of "data"
So hopefully we then have column G telling us the cost
of the call!!
I hope this makes sense, but I will offer an example.
Lets say we are looking in cell D1
of "data" ..... the cell contains the following
i.e. this is a number we called.
F1 = 127
i.e. we called this number for 127 minutes.
Ok so I now look at my column B of the
"codes" worksheet to find out which country this was. I look down the column and I find it in cell B77
(Which is Australia)
So I now know that the call was to Australia.
Now I look in cell C77
So I now know that cell C77 is the value of the cost per minute to Australia.
i.e. This call
would have cost us 14p per minute.
I now multiply cell F1 of "data" worksheet by this value.
127x0.14 = 17.78
So G1 of "data" would display £17.78 and this is the cost of the call!
Wow this was a
I hope one of you geniuses can help with this!
Thanks in advance!