Excell Formula

  • Thread starter Thread starter Farid
  • Start date Start date
F

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 supplier to show cheapest product from each supplier for all
the product


A B
1 Product Supplier
2 Product 1 ?
3 Product 2 ?
4 Product 3 ?
5 Product 4 ?
6 Product 5 ?


Thanks for any help I get
 
In Sheet2 cell B2 apply the below formula

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"


=INDEX(Sheet1!$B$1:$B$1000,MATCH(1,(Sheet1!$A$1:$A$1000=A2)*
(Sheet1!$C$1:$C$1000=MIN(IF(Sheet1!$A$1:$A$1000=A2,
Sheet1!$C$1:$C$1000))),0))
 
Back
Top