complicated lookup formula

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
336
Office Version
  1. 2021
Platform
  1. Windows
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! :)
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
A couple of questions ... you say about the Country ...
{snip} ... Country (Please note, these are in the SAME cell, they are NOT in seperate columns)

Why aren't these in separate columns? Any particular reason?

Is there a one to one relationship between the country text in that column, and the text contained in column B of rates from character 6 onwards? ( e.g. does 225 Ivory Coast (Côte d'Ivoire) in the "codes" sheet have a corresponding entry of:
ISDN-Ivory Coast (Côte d'Ivoire)
i.e. spelling and brackets and so on matching identically )?
 
Upvote 0
I'm working on this now, but you are gonna have a problem with the difference between number lengths. Say a full number to Australia is 11 numbers, would all numbers to eg Cape Verde be 12 numbers or are numbers in Cape Verde different?

Because there is no way you are gonna be able to make a difference between (example) 61-292224623 and 612-92224623 (where numbers before - are countrycodes. In other words; Excel has no way to determine if the country code is 2 or 3 digits. ONLY if numbers with 12 digits have a 3 digit country code, and 11 digit numbers have a 2 digit country code.

From my own experience I know the Netherlands has a 13 digit number if i wanted to call to my parents (2 country code, 2 or 3 area code, 9 or 8 phone number, depending on area code).
Parents is 31 - ### - ######## = 13 digits.

Could you answer this for me?
 
Last edited:
Upvote 0
I got a solution for you, but it will require to cut the sheet "Codes" into 2 columns (country number & country). This will be in A:A and B:B.

The rest can stay the same. This assumes:
- 11 digit numbers have 2 country digits
- OTHER lengths have 3 digit country digits
You'll need to provide an overview of possibilites here to help.
- in "Rates" all countries are in column B look like ISDN-[Country]

I don't have Excel Jeanie installed on this machine, so I hope you can get this to work and understand what I mean.

"Data" has in D2 the phonenumber: 61292224623
F2 holds the amount of minuts: 127
"Codes" holds in A26: 61 (for Australia)
B26 holds: Australia
"Rates" holds B15: ISDN-Australia
C15 holds: 0.14 (rate)

This will go in "Data" G2:
Code:
=F2*VLOOKUP("ISDN-"&RIGHT(VLOOKUP(VALUE(LEFT(D2,IF(LEN(D2)=11,2,3))),
codes!A1:B26,2,FALSE),LEN(VLOOKUP(VALUE((LEFT(D2,IF(LEN(D2)=11,2,3)))),
codes!A1:B26,2,FALSE))),rates!B1:C15,2,FALSE)
HTH.
 
Upvote 0
Hi Glenn,

Thanks for posting :)

A couple of questions ... you say about the Country ...

Why aren't these in separate columns? Any particular reason?

The only reason they are not in seperate columns is because I'm too dumb to figure out the formula to seperate them! So if it help to add a small formula to sepeara them, then yes that's fine to do :)

Is there a one to one relationship between the country text in that column, and the text contained in column B of rates from character 6 onwards? ( e.g. does 225 Ivory Coast (Côte d'Ivoire) in the "codes" sheet have a corresponding entry of:
ISDN-Ivory Coast (Côte d'Ivoire)
i.e. spelling and brackets and so on matching identically )?

To be honest not all match identically, however the amount of entries that don't match is minimal so I could change the ones that don't match, to make them match exactly, if that helps??

Thanks again :)
 
Upvote 0
I got a solution for you, but it will require to cut the sheet "Codes" into 2 columns (country number & country). This will be in A:A and B:B.

The rest can stay the same. This assumes:
- 11 digit numbers have 2 country digits
- OTHER lengths have 3 digit country digits
You'll need to provide an overview of possibilites here to help.
- in "Rates" all countries are in column B look like ISDN-[Country]

I don't have Excel Jeanie installed on this machine, so I hope you can get this to work and understand what I mean.

"Data" has in D2 the phonenumber: 61292224623
F2 holds the amount of minuts: 127
"Codes" holds in A26: 61 (for Australia)
B26 holds: Australia
"Rates" holds B15: ISDN-Australia
C15 holds: 0.14 (rate)

This will go in "Data" G2:
Code:
=F2*VLOOKUP("ISDN-"&RIGHT(VLOOKUP(VALUE(LEFT(D2,IF(LEN(D2)=11,2,3))),
codes!A1:B26,2,FALSE),LEN(VLOOKUP(VALUE((LEFT(D2,IF(LEN(D2)=11,2,3)))),
codes!A1:B26,2,FALSE))),rates!B1:C15,2,FALSE)
HTH.


Thanks for this Stefan, much appreciated! :)

One question, how do I cut the sheet "Codes" into 2 columns?

Could you suggest a formula I could use to acheive this?

thanks
 
Upvote 0
Just select the entire column, go to Data -> Text to Columns, Fixed Width, and then drag the cut-off line to right of the numbers.

I forgot to mention you will have to edit the ranges in my formula, as you only provided me with a limited amount of rows. Mine went to 26 rows or something, if yours go to 6000 (doesn't matter) just edit this. You could change it to A:B, A:B en B:C like this:
Code:
=F2*VLOOKUP("ISDN-"&RIGHT(VLOOKUP(VALUE(LEFT(D2,IF(LEN(D2)=11,2,3))),
codes!A:B,2,FALSE),LEN(VLOOKUP(VALUE((LEFT(D2,IF(LEN(D2)=11,2,3)))),
codes!A:B,2,FALSE))),rates!B:C,2,FALSE)
So it will work no matter how many countries you add.
 
Upvote 0
Stefan I've split the codes into two columns now. I now have column A which is codes and column B which is countries.

Do I copy column A into A:A of the "data" worksheet, and Column B into B:B of the "data" worksheet?

Or can I just copy them into columns A:A and A:B ??


Sorry, I'm getting a bit confused! :)
 
Upvote 0
Maybe it would be easier to somehow first merge the "rates" and "codes" worksheets together?

Any suggestions??
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top