Return corresponding information with minimum value
 

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

Hi Cassy,

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

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

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

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

Cheers.

Spencer.

Hi Spencer,

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

Thanks!

Cassy