MrExcel Publishing
Your One Stop for Excel Tips & Solutions

finding appropriate supplier name in table with articles, suppliers and their prices


Posted by rudi holans on March 27, 2001 3:03 AM

Situation: table left columm: article codes - top row table = supplier names - data = article prices. - I can find the cheapest, 2nd cheapest,.... from the table per row, but now I also need to add the name of the supplier that has the 2nd best price, 3rd best price,.... Which formula do I need to do this. The table has more than 50 columms.

Thx
Rudi HOLANS


Posted by Aladin Akyurek on March 27, 2001 4:58 AM

Assuming Article Codes in A1:A5, Supplier Names in B1:D1. Prices are then in B2:D5

=INDEX(A1:D1,MATCH(SMALL(B2:D2,1),B2:D2,0)+1)

This produces the cheapest supplier name for the article in A2.

Aladin