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!