Excell Formula

[Farid]

I have a list of over 1000 Product in column A and in column B over 30
Supplies and in column C the price of each product from different Supplies.
Each Supplier may offer same product but with different prices ( e.g. Table
below ). All the information is in sheet 1.

A B C
1 Product 1 Supplier 2 £10.00
2 Product 2 Supplier 1 £8.00
3 Product 3 Supplier 2 £8.00
4 Product 2 Supplier 2 £6.00
5 Product 1 Supplier 3 £11.00
6 Product 3 Supplier 1 £7.00


I have created sheet 2 ( e.g. table below ) I need formula which updates
under heading of each supplier to show which product is cheapest from each
supplier for all the product

A B C
1 Supplier 101 Supplier 2 Supplier 3
2 Product 100 Product 14 Product 16
3 Product 9 Product 25 Product 111
4 Product 590 Product 89 Product 2
5 Product 250 Product 56 Product 15
6 Product 1 Product 999 Product 8

Thanks for any help I get

 

[John]

Hi Farid
I'm not sure if I got the column right but you can change that. Also adjust
range to your needs.
=SUMPRODUCT(--(Sheet1!B2:B7="Product1"),--(Sheet1!A2:A7="Supplier1"),C2:C7)
column C would be your prices.
HTH
John

 

[John]

Ignore my reply,it's adding up, not giving you the cheapest, I didn't read
properly
Regards
John