complicated lookup formula

[jpowell79]

Hi Guys,

I was wondering if anyone could help me with a tricky lookup formula??

Ok I have an Excel workbook with 3 worksheets, labelled as "rates", "codes" and "data"

The "rates" worksheet contains three columns (A, B, C) which contain call rates, based upon country codes.

Column A is not really relevant, but column B lists countries. Here is an example of data from column B

ISDN-Austria
ISDN-Belgium
ISDN-Canada
ISDN-Denmark
ISDN-Finland
ISDN-France
ISDN-Germany
ISDN-Ireland
ISDN-Italy
ISDN-Luxembourg
ISDN-Netherlands
ISDN-Norway
ISDN-Spain


Now adjacent to these cells (in Column C) there is the call rates (in pence) for dialling these countries per minute.

So column C adjacent to these would show:

0.06
0.06
0.06
0.06
0.06
0.06
0.06
0.06
0.06
0.06
0.06
0.06
0.06


So as you can see, it would cost 6p per minute to call Austria.


Ok! Still with me??

Great!!

Right so moving onto the next worksheet, "codes"

This worksheet contains a single column of data (column A) with country dialling codes followed by the Country (Please note, these are in the SAME cell, they are NOT in seperate columns) so below is an example of data found in column A

20 Egypt
212 Morocco
213 Algeria
216 Tunisia
218 Libya
220 Gambia
221 Senegal
222 Mauritania
223 Mali
224 Guinea
225 Ivory Coast (Côte d'Ivoire)
226 Burkina Faso
227 Niger
228 Togo
229 Benin
230 Mauritius (incl Rodriguez Island)
230 Rodriguez Island
231 Liberia
232 Sierra Leone
233 Ghana
234 Nigeria
235 Chad
236 Central African Republic
237 Cameroon
238 Cape Verde


Ok, finally I have a worksheet called "data" and this worksheet is the worksheet which tells us The telephone numbers of the calls we've made. This is 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 A of the "codes" worksheet, to determine the country code and what country the call was made to
3) Now we know what country we called, we can now lookup column B of the "rates" worksheet to check the Country, and adjacent to this (column C) will tell us the cost per minute of the call.
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

D1= 61292224623

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 A of the "codes" worksheet to find out which country this was. I look down the column and I find in cell A77

A77= 61 Australia

So I now know that the call was to Australia.

Now I look in column B of the "rates" worksheet and I find in cell A29

A29= ISDN-Australia

So I now know that cell B29 is the value of the cost per minute.

So B29=0.14

i.e. This call would have cost us 14p per minute.

I now multiply cell F1 of "data" worksheet by this value.

i.e. 127x0.14 = 17.78

So G1 of "data" would display £17.78 and this is the cost of the call!


Wow this was a long one!!

I hope one of you geniuses can help with this!

Thanks in advance!

 

[dafan]

Sorry if I was unclear.

I just the exact same layout you had before, but with 1 alteration:

In "Codes"
Column A looks like this:
20
212
213
214
etc

Column B looks like this:
Egypt
Morocco
Algeria
Tunisia
etc

My comment about the formula doesn't have anything to do with the layout of your sheet, just an alternative to make it more flexible.

Ill be awake for another 30 mins, then you're gonna have to wait some hours for a new answer I've bookmarked this thread so just fire away and I'll get to them in the morning if no-one else answered it

 

[dafan]

Don't forget to answer this question I asked before:
http://www.mrexcel.com/forum/showpost.php?p=1607479&postcount=3

What I build now will only work for 2 digit country numbers.