Comparing rates

[Scubabermuda]

Good Morning,

Need some assistance hope someone here can help me. I am trying to use Excel to compare the values and generate the least cost for a phone number.

The values in column A may not necessarily match column E or be in order for that matter. They are rates from 2 different vendors. Essentially they are the first 6 digits of a phone number and columns B and F are the rates associated with those codes.

So what I am trying to do here is have excel look at the code in A and its associated price in B - compare it to E and F and give me the the code in H and the best rate for that code in I. Have I totally confused you guys here?

This was worth a shot asking all you gurus here. If you can help me I would really appreciate it.

 

[VoG]

You need to enter a code in H2

 

[scottylad2]

The order doesn't matter here, The list in I will deliver the lowest when there are same values in A and E, i assumed there always will be

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	CODE	RATE			CODE	RATE		Code	RATE
2	201007	0.00875			201007	0.0098		201007	0.00875
3	201032	0.01375			201032	0.0122		201032	0.0122
4	201040	0.00525			201040	0.0049		201040	0.0049
5	201200	0.0045			201200	0.0042		201200	0.0042
6	201202	0.003875			201202	0.0024		201202	0.0024
7	201203	0.008875			201203	0.0068		201203	0.0068
8	201204	0.006125			201204	0.0048		201204	0.0048
9	201206	0.002			201206	0.0016		201206	0.0016
10	201207	0.002			201207	0.0016		201207	0.0016
11	201208	0.002			201208	0.0016		201208	0.0016
12	201209	0.005125			201209	0.0042		201209	0.0042
13	201210	0.00625			201210	0.0054		201210	0.0054
14	201213	0.002			201213	0.0016		201213	0.0016
15	201214	0.002			201214	0.0016		201214	0.0016
16	201215	0.00825			201215	0.007		201215	0.007
17	201216	0.005125			201216	0.0042		201216	0.0042
18	201217	0.005125			201217	0.0042		201217	0.0042
19	201218	0.002			201218	0.0016		201218	0.0016
20	201219	0.003875			201219	0.0024		201219	0.0024
21	201220	0.002			201220	0.0016		201220	0.0016
Sheet2

 

[Scubabermuda]

You need to enter a code in H2

duh.....you are a freaking life saver thank you so much.

 

[Scubabermuda]

Last question I promise.

If there is a value missing in A or E (a phone number code for example not all vendors cover the same coverage area) is there a way to have excel populate H with the rate from F or B for that code?

So for example

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	CODE	RATE			CODE	RATE		CODE	RATE
2	201007	0.0072			201200	0.0023		201007	0.0072
3	201032	0.0106			201201	0.0252
4	201040	0.004			201202	0.0026
5	201200	0.0039			201203	0.00374
6	201202	0.0029			201204	0.003
7	201203	0.0076			201205	0.0015
Sheet1

So since code 201007 is not listed in E it will default to A2 and the rate in B2 in H and I

 

[Scubabermuda]

Scotty A & E will not necessarily always match. Like I mentioned above. As a matter of fact there will be variations for the values for A & E. Some codes will be present in A and may not be in E

 

[scottylad2]

The code i posted will look up the code in column H and return the lowest rate based on that number. If it's only availabli in one of the columns then it will show that rate

 

[Scubabermuda]

Getting zero values in I

 

[Scubabermuda]

But as you explained that is exactly what I need here

 

[Scubabermuda]

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	CODE	RATE			CODE	RATE		CODE	RATE
2	201007	0.0072			201200	0.0023		201007	0
3	201032	0.0106			201201	0.0252		201032	0
4	201040	0.004			201202	0.0026		201040	0
5	201200	0.0039			201203	0.00374		201200	0
6	201202	0.0029			201204	0.003		201202	0
7	201203	0.0076			201205	0.0015		201203	0
8	201204	0.0048			201206	0.0014		201204	0
9	201205	0.0016			201207	0.0012		201205	0
10	201206	0.0015			201208	0.0014		201206	0
11	201207	0.0015			201209	0.0023		201207	0
12	201208	0.0015			201210	0.0032		201208	0
13	201209	0.0039			201211	0.0252		201209	0
14	201210	0.0045			201213	0.0012		201210	0
15	201213	0.0015			201214	0.0014		201213	0
16	201214	0.0015			201215	0.0035		201214	0
17	201215	0.0061			201216	0.0023		201215	0
18	201216	0.0039			201217	0.0023		201216	0
19	201217	0.0039			201218	0.0014		201217	0
20	201218	0.0015			201219	0.0026		201218	0
21	201219	0.0029			201220	0.0014		201219	0
Sheet1

 

[scottylad2]

Enter with Ctrl + Shift + Enter, not kust enter