Creating Formula in Excel to Select Lowest bidder(s) (1st, 2nd, 3rd)

[fserrano]

I am Creating a bid review spreadsheet to select low bidders and input their name in a given cell. I have standardized the bid form and already carried over all data to cell range BV:CS which should help. In the given range there are cells that contain zeros.

This is the first of many potentially complex formulas, some of which I believe will require Macros... any and all help is greatly appreciated!

Data Points:

M9 to show 1st Place
N9 to show 2nd Place
O9 to show 3rd Place

Bidder Names are located in range BV8:CS8
Bidder Values are located in range BV9:CS9

Thanks Again for your help!

 

[fserrano]

There may be a better way, but this should work.

In M9:
=IF($L$9="M",INDEX($BV$8:$CS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+1),$BV$9:$CS$9,0)),IF($L$9="W",INDEX($AX$8:$BU$9,1,MATCH(SMALL($AX$9:$BU$9,COUNTIF($AX$9:$BU$9,0)+1),$AX$9:$BU$9,0)),IF($L$9="D",INDEX($AX$8:$BU$9,1,MATCH(SMALL($Z$9:$AW$9,COUNTIF($Z$9:$AW$9,0)+1),$Z$9:$AW$9,0)),"")))


In N9:
=IF($L$9="M",INDEX($BV$8:$CS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+2),$BV$9:$CS$9,0)),IF($L$9="W",INDEX($AX$8:$BU$9,1,MATCH(SMALL($AX$9:$BU$9,COUNTIF($AX$9:$BU$9,0)+2),$AX$9:$BU$9,0)),IF($L$9="D",INDEX($AX$8:$BU$9,1,MATCH(SMALL($Z$9:$AW$9,COUNTIF($Z$9:$AW$9,0)+2),$Z$9:$AW$9,0)),"")))


In O9:
=IF($L$9="M",INDEX($BV$8:$CS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+3),$BV$9:$CS$9,0)),IF($L$9="W",INDEX($AX$8:$BU$9,1,MATCH(SMALL($AX$9:$BU$9,COUNTIF($AX$9:$BU$9,0)+3),$AX$9:$BU$9,0)),IF($L$9="D",INDEX($AX$8:$BU$9,1,MATCH(SMALL($Z$9:$AW$9,COUNTIF($Z$9:$AW$9,0)+3),$Z$9:$AW$9,0)),"")))


I hope for you sake that you won't need to edit these .

Works EXCELLENT!

This is my first time utilizing formulas this complex... The one thing I did do right in my mind is creating a summary sheet per say, all of this data is being pulled from over 30 other spreadsheets. The good thing is that when the time comes to update the spreadsheets the base formulas in the summary page will not change.

Is there a way to carry these formulas down the column without having to go in and key in the correct range? Typically when you select a cell (M9 for example) and drag it down column M to M10,M11,M12, Ect the formula will adapt to the correct row number. It is not working in this case.

 

[bbott]

The reason it doesn't change when you copy down is because the ranges are locked with the $ symbol. If you remove all of the $ from the formula, it will change as you copy it down. But the range will also change if you add or remove rows and/or columns, so it's usually a good idea to lock ranges when using large formulas.

Long story short, sometime you'll get unintended results if the ranges are not locked.

 

[fserrano]

The reason it doesn't change when you copy down is because the ranges are locked with the $ symbol. If you remove all of the $ from the formula, it will change as you copy it down. But the range will also change if you add or remove rows and/or columns, so it's usually a good idea to lock ranges when using large formulas.

Long story short, sometime you'll get unintended results if the ranges are not locked.

Perfect, so if I want to lock the range showing "vendor description" Z8:AW8 but want the remaining references that are searching for the data relating to each vendor to change I should input:

=IF($L$9="M",INDEX($BV$8:$CS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+1),$BV$9:$CS$9,0)),IF($L$9="W",INDEX($AX$8:$BU$9,1,MATCH(SMALL($AX$9:$BU$9,COUNTIF($AX$9:$BU$9,0)+1),$AX$9:$BU$9,0)),IF($L$9="D",INDEX(AX8:BU9,1,MATCH(SMALL(Z9:AW9,COUNTIF(Z9:AW9,0)+1),Z9:AW9,0)),"")))

 

[bbott]

Perfect, so if I want to lock the range showing "vendor description" Z8:AW8 but want the remaining references that are searching for the data relating to each vendor to change I should input:

=IF($L$9="M",INDEX($BV$8:$CS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+1),$BV$9:$CS$9,0)),IF($L$9="W",INDEX($AX$8:$BU$9,1,MATCH(SMALL($AX$9:$BU$9,COUNTIF($AX$9:$BU$9,0)+1),$AX$9:$BU$9,0)),IF($L$9="D",INDEX(AX8:BU9,1,MATCH(SMALL(Z9:AW9,COUNTIF(Z9:AW9,0)+1),Z9:AW9,0)),"")))

Without having your sheet in front of me, it's difficult to say. My recommendation would be to save a copy of your spreadsheet, make some changes and copy down, then look at the formula and see what changed. You can also use the Trace Precedents feature to see where the formula is getting it's input from.

 

[markmzz]

Try this:

In M9

=INDEX(IF($L$9="D",$Z$8:$AW$8,IF($L$9="W",$AX$8:$BU$8,$BV$8:$CS$8)),,
MATCH(SMALL(IF($L$9="D",$Z9:$AW9,IF($L$9="W",$AX9:$BU9,$BV9:$CS9)),COUNTIF(IF($L$9="D",$Z9:$AW9,IF($L$9="W",$AX9:$BU9,$BV9:$CS9)),0)+
COLUMNS($M9:M9)),IF($L$9="D",$Z9:$AW9,IF($L$9="W",$AX9:$BU9,$BV9:$CS9)),0))

And copy to right and down.

Markmzz