finding lowest cost from supplier list

**jimbokeep** · 08-04-2010, 11:16 AM

Hello everyone, hopefuly someone can help me, with a variation of a problem i was having before.
I've attached an example spreadsheet which i hope will help explain what i am trying to do, but to basically explain, i'm trying to create a formula to find the lowest cost price of a product from a list of multiple suppliers, while excluding suppliers from a specific location.

On the sheet titled Raw Data, i have an example list of pricing from each supplier by destiantion. on the sheet titled Final Cost, i want to be able to use a formula to find the lowest cost from the Raw data sheet, but exlcuding the pricing from suppliers in Shanghai.

**sweep** · 08-04-2010, 11:32 AM

Hi,

This array in the final cost sheet, cell B2 and copied down

=MIN(IF('Raw data'!$A$2:$A$14&'Raw data'!$C$2:$C$14='Final Cost'!A2&"Shanghai",'Raw data'!$D$2:$D$14,""))

Confirm with CTRL, SHIFT and ENTER

**jimbokeep** · 08-04-2010, 11:45 AM

Thanks for the response, and the formulae you have provided is actually better than a similar one i am using elsewhere, however i don't think it solves the issue i was having.
I'm trying to find the lowest price from the list but excluding prices from Shanghai, and i think this formula will actually find me the price from Shanghai.
If you could help out here again it would be very much appreciated.

**sweep** · 08-04-2010, 01:48 PM

Sorry - had misread.

Try this:

=MIN(IF('Raw data'!$A$2:$A$14='Final Cost'!A2,IF(NOT('Raw data'!$C$2:$C$14="Shanghai"),'Raw data'!$D$2:$D$14,"")))

also an array, CTRL, SHIFT and ENTER