how about?
in cell i3 (for row 3 with names in row 2)
=OFFSET($A2,0,MATCH(SMALL(IF($B$3:$H$30,$B$3:$H$3 ),1),$B$3:$H$3,0))
in cell j3
=OFFSET($A2,0,MATCH(SMALL(IF($B$3:$H$30,$B$3:$H$3 ),2),$B$3:$H$3,0))
both are array formulas
the idea is to id the 2 smallest non zero entries in row 3, and find the
corresponding name in row 2 with the offset/match combo
does this work for you?
"Bullman" wrote:
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