Excell Formula

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
 
J

Jacob Skaria

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top