How to find where result came from comparing to tables using index & min formula

[excel2007uk]

Hi,

Im not sure if this is possible or if you can think of a way changing a few things so I can do this...

Basically what I'm trying to do is compare 2 tables of prices in sheet 6 then get the cheapest result into sheet 1 and see what suppliers price it is .

I have attempted this by having both price lists with approximately 200 different prices in each in page 6 to the right hand side the on the left I have used the formula =MIN(Q3,Z3) in every cell in the comparison table.

In sheet 1 I have then got the following formula, which basically takes the product spec from sheet 1 (0201 BC) & finds it it the comparison table in sheet 6 (Board Prices) =INDEX('Board Prices'!$C$3:$H$100,MATCH('0201 BC'!$B$14&'0201 BC'!$B$15,'Board Prices'!$A$3:$A$100&'Board Prices'!$B$3:$B$100,0),MATCH($C$28,'Board Prices'!$C$2:H$2,1))

I have several other sheets that use the exact same formula to find the cheapest result in sheet 6.

My problem is in sheet one I never know which the cheapest prices has come from, so I still have to manually find it. In an ideal world i would like the name of the supplier to appear in sheet one, but if it was colour coded this wouldn't be a problem

My knowledge of excel is quite basic so any opinions of if this is even possible or how I can achieve this would be VERY much appreciated

 

[steve the fish]

Your data set up really doesnt help you. Why not create one table with all your prices and have the headers, Product Code, Supplier, Price. You then can use a pivot table the easily see the cheapest supplier against each product and its price.