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))

--
Jacob


"Farid" wrote:

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

Thank You veru much you are fab,genies. worked perfect

"Jacob Skaria" wrote:

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))

--
Jacob


"Farid" wrote:

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