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]

Your picture is illegible plus we cannot copy and paste that into Excel (except as a picture).

Try using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[Scubabermuda]

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
3	201032	0.01375			201032	0.0122
4	201040	0.00525			201040	0.0049
5	201200	0.0045			201200	0.0042
6	201202	0.003875			201202	0.0024
7	201203	0.008875			201203	0.0068
8	201204	0.006125			201204	0.0048
9	201206	0.002			201206	0.0016
10	201207	0.002			201207	0.0016
11	201208	0.002			201208	0.0016
12	201209	0.005125			201209	0.0042
13	201210	0.00625			201210	0.0054
14	201213	0.002			201213	0.0016
15	201214	0.002			201214	0.0016
16	201215	0.00825			201215	0.007
17	201216	0.005125			201216	0.0042
18	201217	0.005125			201217	0.0042
19	201218	0.002			201218	0.0016
20	201219	0.003875			201219	0.0024
21	201220	0.002			201220	0.0016
Sheet1

 

[Scubabermuda]

Let me reiterate values in A and & E may not be in the same order

 

[VoG]

With a code in H2 try in I2

=MIN(VLOOKUP(H2,A$2:B$21,2,FALSE),VLOOKUP(H2,A$2:B$21,2,FALSE))

 

[Scubabermuda]

I pasted the code in H2 getting 0 value. Tried it in I2 getting NA

 

[Scubabermuda]

Actually when I pasted into H2 its says not valid code error message.

 

[VoG]

Sorry there was an error in my formula

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
Sheet4

 

[VoG]

Actually when I pasted into H2 its says not valid code error message.

If you use , as the decimal separator, replace , with ; in the formula.

 

[Scubabermuda]

Still getting NA

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			#N/A
3	201032	0.01375			201032	0.0122
4	201040	0.00525			201040	0.0049
5	201200	0.0045			201200	0.0042
6	201202	0.003875			201202	0.0024
7	201203	0.008875			201203	0.0068
8	201204	0.006125			201204	0.0048
9	201206	0.002			201206	0.0016
10	201207	0.002			201207	0.0016
11	201208	0.002			201208	0.0016
12	201209	0.005125			201209	0.0042
13	201210	0.00625			201210	0.0054
14	201213	0.002			201213	0.0016
15	201214	0.002			201214	0.0016
16	201215	0.00825			201215	0.007
17	201216	0.005125			201216	0.0042
18	201217	0.005125			201217	0.0042
19	201218	0.002			201218	0.0016
20	201219	0.003875			201219	0.0024
21	201220	0.002			201220	0.0016
Sheet1