# Formula for Price Comparison

#### Bitty

##### New Member
 Company 1 Company 2 Company 3 Company 4 Destination Origin 0 (Min \$) 100 lbs. 1000 lbs. 0 (Min \$) 100 lbs. 500 lbs. 0 (Min \$) 100 lbs. 500 lbs. 0 (Min \$) 100 lbs. 500 lbs. DFW ATL \$25.95 \$7.76 \$7.12 \$39.82 \$13.36 \$13.09 \$40.00 \$11.13 \$10.89 \$26.00 \$10.47 \$9.97 CVG CLT \$34.60 \$13.80 \$13.80 \$39.82 \$23.58 \$23.12 \$40.00 \$19.00 \$19.00 \$39.50 \$20.94 \$19.95 GSP EWR \$45.00 \$11.25 \$10.50 \$39.82 \$21.81 \$20.82 \$40.00 \$19.00 \$19.00 \$33.50 \$21.64 \$19.95

<tbody>
</tbody>

Hello All,

I am attempting to put together a price comparison spreadsheet, but I am having trouble figuring a formula to return the necessary information. There is approximately 9,000 Origin/Destination combinations to be searched in the formula. What I need is:

1.) A formula to search for the origin, destination, and closest weight.
2.) I then need the formula to return the lowest cost (of four competing carrier's).
3.) I will then need a second formula to return the name of the carrier.
4.) If possible I would like to rank all four carrier's in descending order.

I am unsure of how to post an example of my worksheet. I believe seeing an example would help tremendously. Please reply, if only to help with posting example.

Thank you,

Bitty

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Rijnsent

##### Well-known Member
Hi Bitty,

I assume you have 3 cells with the "input data": Origin, Destination and weight.

Firstly, to simplify the processing I would create an extra column in your data, linking the origin and destination, like "DFW-ATL" (the formula for that is e.g. =B3&"-"&C3, that would be a package from ATL to DFW). If you do that with your input data too, you could use a simple MATCH formula to lookup the row where the right information is (the prices that is).

Secondly, the pricing. I am not really getting what the prices would be, but I guess the easiest would be to calculate the 4 prices (you can get the numbers with the MATCH result and an OFFSET formula) and take the MIN. With another match and offset you could find out which of the 4 companies that would be.

I hope those pointers help you going, if not, don't hesitate to respond. And if you could then give an example of how you would e.g. calculate the price of a 150 lbs or 1230 lbs package, that would be helpfull.

Cheers,

Koen

#### Bitty

##### New Member
Thank you Rijnsent. I have found a formula that works for this spreadsheet. I appreciate your help.

Replies
6
Views
297
Replies
6
Views
218
Replies
14
Views
338
Replies
1
Views
312
Replies
2
Views
388

1,170,931
Messages
5,872,772
Members
432,944
Latest member
mj02

### 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.

### Which adblocker are you using?

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

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