Return corresponding information with minimum value

[CassyM]

Hello,

I have the following formula that will provide me with a freight rate:

=IF(OR('Producing Mills'!J5="Y",'Producing Mills'!L5="Y",'Producin
Mills'!N5="Y",'Producing Mills'!O5="Y",'Producin
Mills'!R5="Y",'Producing Mills'!S5="Y",'Producin
Mills'!V5="Y"),MIN((IF('Producing Mills'!J5="Y",'Freight & Exchang
Rates'!$D$13,10000)),(IF('Producing Mills'!L5="Y",'Freight & Exchang
Rates'!$D$15,10000)),(IF('Producing Mills'!N5="Y",'Freight & Exchang
Rates'!$D$17,10000)),(IF('Producing Mills'!O5="Y",'Freight & Exchang
Rates'!$D$18,10000)),(IF('Producing Mills'!R5="Y",'Freight & Exchang
Rates'!$D$21,10000)),(IF('Producing Mills'!S5="Y",'Freight & Exchang
Rates'!$D$22,10000)),(IF('Producing Mills'!V5="Y",'Freight & Exchang
Rates'!$D$25,10000))),MIN((IF('Producing Mills'!C5="Y",'Freight
Exchange Rates'!$D$7,10000)),(IF('Producing Mills'!D5="Y",'Freight
Exchange Rates'!$D$6,10000))))

Essentially it checks if any of our competitors make the product, and i
they do, chooses the minimum freight rate among the competitors that d
make it; if none of our competitors make the product, it chooses th
minimum freight rate from our plant locations that produce the product


What I'd like to do is also show who's freight we are using, which woul
be in column A of the Freight & Exchange Rates worksheet.

I have tried to achieve this by using the VLOOKUP to match the freigh
rate amount to the rates listed in the Freight & Exchange Rate
worksheet, but the problem is that there could be multiple competitor
with the same freight rate, some of whom do not produce the product. S
I need to take into account whether or not they actually produce th
product (by looking at the Producing Mills worksheet) in order to matc
up the rate.

I am really at a loss as to how to proceed, I've thought maybe I coul
try to get the cell address of the min value and then use the offse
function to return the company name of the freight rate, but have bee
unsuccessful.

I apologize for the long post, but wanted to make sure I explained m
problem correctly, and hopefully didn't leave anything out.

Any ideas/help is greatly appreciated!

Cass

 

[Spencer101]

Hello,

I have the following formula that will provide me with a freight rate:

=IF(OR('Producing Mills'!J5="Y",'Producing Mills'!L5="Y",'Producin
Mills'!N5="Y",'Producing Mills'!O5="Y",'Producin
Mills'!R5="Y",'Producing Mills'!S5="Y",'Producin
Mills'!V5="Y"),MIN((IF('Producing Mills'!J5="Y",'Freight & Exchang
Rates'!$D$13,10000)),(IF('Producing Mills'!L5="Y",'Freight & Exchang
Rates'!$D$15,10000)),(IF('Producing Mills'!N5="Y",'Freight & Exchang
Rates'!$D$17,10000)),(IF('Producing Mills'!O5="Y",'Freight & Exchang
Rates'!$D$18,10000)),(IF('Producing Mills'!R5="Y",'Freight & Exchang
Rates'!$D$21,10000)),(IF('Producing Mills'!S5="Y",'Freight & Exchang
Rates'!$D$22,10000)),(IF('Producing Mills'!V5="Y",'Freight & Exchang
Rates'!$D$25,10000))),MIN((IF('Producing Mills'!C5="Y",'Freight
Exchange Rates'!$D$7,10000)),(IF('Producing Mills'!D5="Y",'Freight
Exchange Rates'!$D$6,10000))))

Essentially it checks if any of our competitors make the product, and i
they do, chooses the minimum freight rate among the competitors that d
make it; if none of our competitors make the product, it chooses th
minimum freight rate from our plant locations that produce the product


What I'd like to do is also show who's freight we are using, which woul
be in column A of the Freight & Exchange Rates worksheet.

I have tried to achieve this by using the VLOOKUP to match the freigh
rate amount to the rates listed in the Freight & Exchange Rate
worksheet, but the problem is that there could be multiple competitor
with the same freight rate, some of whom do not produce the product. S
I need to take into account whether or not they actually produce th
product (by looking at the Producing Mills worksheet) in order to matc
up the rate.

I am really at a loss as to how to proceed, I've thought maybe I coul
try to get the cell address of the min value and then use the offse
function to return the company name of the freight rate, but have bee
unsuccessful.

I apologize for the long post, but wanted to make sure I explained m
problem correctly, and hopefully didn't leave anything out.

Any ideas/help is greatly appreciated!

Cassy

Hi Cassy,

Sounds possible, but the explanation is a little complicated to fatho
exactly.

Any chance you could send a copy of the workbook you have so far? Yo
can email it to me if you like.

It will need to have some representative data in it. It doesn't have t
be actual data, but certainly plausible data.

You can change company names and things like that if you do not wan
them known.

Cheers.

Spencer

 

[CassyM]

Hi Spencer,

Thank you for offering to help! I've attached a sample of th
spreadsheet in hopes that it will make more sense.

Thanks!

Cass

+-------------------------------------------------------------------
|Filename: Copy of Pricing Breakdown.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=314
+-------------------------------------------------------------------