Excel Function for Choosing and Analyse Best Supplier

[peergynt]

[FONT=&quot]Hello All.[/FONT]
[FONT=&quot]I have a question. This is what I want to do;[/FONT]
[FONT=&quot]I have 3 or more suppliers and every supplier gives me price list for items. I compile them into one excel file. I could choose only one supplier and I need to buy all items from that. [/FONT]
[FONT=&quot]For example, I have item list on the first column, Supplier A's prices in the second column, Supplier B's prices on third one etc. [/FONT]
[FONT=&quot]I would like to add extra columns to find out which supplier is best for that item and help me to analyze between suppliers.[/FONT]
[FONT=&quot]I would like to add one column for; check three prices and find minimum one and write it that cell. [/FONT]
[FONT=&quot]For another extra column; indicate this min. price belong to Supp.A or Supp.B or Supp.C[/FONT]
[FONT=&quot]And the hard part that I cannot figure it out; another column to make analyze.[/FONT]
[FONT=&quot]For example, I will write selected supplier on one cell like A, B or C[/FONT]
[FONT=&quot]An extra column for analyzing need to be; look selected supplier, compare its price with other two suppliers item by item. [/FONT]
[FONT=&quot]if min. price belongs to the selected supplier it will write to cell "Cheapest" [/FONT]
[FONT=&quot]if min. price of this item not belong to selected supplier; it will compare with other two suppliers price, if difference less than 10% between selected one and other two supplier price it will write "Not Cheapest but Safe", If the price difference more than 10% between selected one and either one of the other two suppliers it will write "Not Cheapest and Need Check"[/FONT]
[FONT=&quot]What do you think? Is this doable?

[/FONT]

 

[Legacy 143009]

I didn't check it. I hope it works.

G4 =IF(SMALL($B4:$D4,2)>INDEX($B$:$D$11,ROW(),MATCH($B$1,$B$3:$D$3,0))*1.1,"NOT THE CHEAPEST","CHEAPEST")