Hello,

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

=IF(OR('Producing Mills'!J5="Y",'Producing Mills'!L5="Y",'Producing Mills'!N5="Y",'Producing Mills'!O5="Y",'Producing Mills'!R5="Y",'Producing Mills'!S5="Y",'Producing Mills'!V5="Y"),MIN((IF('Producing Mills'!J5="Y",'Freight & Exchange Rates'!$D$13,10000)),(IF('Producing Mills'!L5="Y",'Freight & Exchange Rates'!$D$15,10000)),(IF('Producing Mills'!N5="Y",'Freight & Exchange Rates'!$D$17,10000)),(IF('Producing Mills'!O5="Y",'Freight & Exchange Rates'!$D$18,10000)),(IF('Producing Mills'!R5="Y",'Freight & Exchange Rates'!$D$21,10000)),(IF('Producing Mills'!S5="Y",'Freight & Exchange Rates'!$D$22,10000)),(IF('Producing Mills'!V5="Y",'Freight & Exchange 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 if they do, chooses the minimum freight rate among the competitors that do make it; if none of our competitors make the product, it chooses the 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 would be in column A of the Freight & Exchange Rates worksheet.

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

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

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

Any ideas/help is greatly appreciated!

Cassy