Hello,

I have a list of ithe names of the tems I have had quoted on by
suppliers in colum range A2:A10. I have a row of the names of
suppliers in cells B1:H1. The supplier quoted prices fill the cells
B2:H10. Not all suppliers have quoted on every item so some cells are
blank (zero).

I have been trying to get Excel to tell me the "Best Priced" and
"Second Best Priced" supplier name for each item in the cell ranges
I2:I10 and J2:J10 respectively, without it selling zero/blank cells as
the lowest price.

The limitation on nested if formulas has prevented me from using a
forumla like this to determine the Best Priced supplier name for Item
listed in A1:

=IF($B2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$B$1,I F($C2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$C$1,IF( $D2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$D$1,IF($E 2=SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2= SMALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SM ALL($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,IF($E2=SMAL L($B2:$H2,COUNTIF($B2:$H2,0)+1),$E$4,"ERROR")))))) )

Note: the COUNTIF(x,0) is required to prevent any blanks or zeros from
being counted as the "Best Price"/lowest price.

This formula would work otherwise.

I have tried using the work around mentioned here
(http://www.cpearson.com/excel/nested.htm) but fo rsome reason I have
been getting #VALUE! error.

Would be great it someone could a work around.

TIA

Bullman