Creating a Bid Sheet need to pick lowest Bidder

[dj_mix]

Quick example:

I have

 
           #1                             #2                            #3 
quanity  unit price  total        quanity  unit price  total       quanity  unit price  total 
2           10            20           3           10           30            5          10         50 
5           10            50           4           10           40            2          10         20  
6           10            60           7           10           70            1          10         10 
 
          subtotal      130                   subtotal      140                 subtotal        80  

I would like to copy the unit price (Entire Column) from the lowest bider (Subtotal) to another sheet ... I have done this with if statements before but I'm limited to 7 nested if's... is there another way to do this sometimes I have more ten 7 bidders.

Thanx once more...

[Nik]

=small(c7:c17,1)

where c7:c17 is your array and 1 is the nth smallest
if you enter 4 instead of 1 you get the fourth smallest number in your list

[dj_mix]

=small(c7:c17,1)

where c7:c17 is your array and 1 is the nth smallest
if you enter 4 instead of 1 you get the fourth smallest number in your list

Not sure you if understood what I wanted to do so I will explain it a bit more..

Lets say I have 3 bidders as in the example, I need to look at the subtotal for the smallest amount and then take the entire column in the unit prive from the lowest amount to another sheet.

I used you small formula and it worked to display the smallest subtotal but how do I incorpate it to copy a entire column corresponding to the loewst subtotal ?

[dj_mix]

anyone can throw me ideas??

maybe use vlookup ? with another function..

maybe counta ??

[dj_mix]

Ok I got someting to work using sumproduct

=SUMPRODUCT((J14:AQ14=D14)*(I7:AP7)

but when I drag it down to recreate the next cells it get

=SUMPRODUCT((J15:AQ15=D15)*(I8:AP8)

is there a way to get it to drag but look like this ?

=SUMPRODUCT((J14:AQ14=D14)*(I8:AP8))

[Nik]

Yes, this can be corrected with minor changes.
Use $'s.
They lock cell addresses.

=SUMPRODUCT(($J$14:$AQ$14=$D$14)*(I7:AP7)

[dj_mix]

Yes, this can be corrected with minor changes.
Use $'s.
They lock cell addresses.

=SUMPRODUCT(($J$14:$AQ$14=$D$14)*(I7:AP7)

perfect worked great thanx