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!

 

[bbott]

Welcome to the board.

Try these formulas:

In M9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,1),$BV$9:$CS$9,0))

In N9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,2),$BV$9:$CS$9,0))

In O9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,3),$BV$9:$CS$9,0))

 

[bbott]

Sorry, missed that the range includes zeros.

In M9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+1),$BV$9:$CS$9,0))

In N9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+2),$BV$9:$CS$9,0))

In O9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+3),$BV$9:$CS$9,0))

 

[fserrano]

Thank you for the Welcome!

I entered this formula as an array and it selects the first entry in the range that shows a value of "0".

If I change all of the "0"'s in the range to a number >0 it will select the lowest.

Tried changing it formula to read:

=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,1),$BV$9:$CS$9>"0")) NO LUCK

For the most part this is what I need, but those Zeros are getting me again.

 

[fserrano]

THAT WORKED GREAT!

Thank you for your help!

 

[bbott]

Thank you for the Welcome!

I entered this formula as an array and it selects the first entry in the range that shows a value of "0".

If I change all of the "0"'s in the range to a number >0 it will select the lowest.

Tried changing it formula to read:

=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,1),$BV$9:$CS$9>"0")) NO LUCK

For the most part this is what I need, but those Zeros are getting me again.

Try my second post, I think that should work.

 

[bbott]

THAT WORKED GREAT!

Thank you for your help!

Glad I could help!

 

[fserrano]

Taking this a step further:

In Cell "L9" we are entering D,W,M

Can this formula be reconfigured to pull data from range:

Z8:AW8, Z9:AW9 if L9= "D"
AX8:BU8,AX9:BU9, if L9= "W"
BV8:CS8, BV8:CS8, if L9= "M"

 

[fserrano]

Sorry, missed that the range includes zeros.

In M9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+1),$BV$9:$CS$9,0))

In N9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+2),$BV$9:$CS$9,0))

In O9:
=INDEX($BV$8:$BCS$9,1,MATCH(SMALL($BV$9:$CS$9,COUNTIF($BV$9:$CS$9,0)+3),$BV$9:$CS$9,0))

Taking this a step further:

In Cell "L9" we are entering D,W,M

Can this formula be reconfigured to pull data from range:

Z8:AW8, Z9:AW9 if L9= "D"
AX8:BU8,AX9:BU9, if L9= "W"
BV8:CS8, BV8:CS8, if L9= "M"

 

[bbott]

Taking this a step further:

In Cell "L9" we are entering D,W,M

Can this formula be reconfigured to pull data from range:

Z8:AW8, Z9:AW9 if L9= "D"
AX8:BU8,AX9:BU9, if L9= "W"
BV8:CS8, BV8:CS8, if L9= "M"

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($Z$8:$AW$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($Z$8:$AW$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($Z$8:$AW$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 .

Edit: Changed one of the ranges for scenario 'D'.