Harlan Grove wrote...
...
Using a 20-row sample data range in A2:B21 and the following
Bonus range *INCLUDING* the zero value as the final entry,
...
and changing my cell C2 formula to

=INDEX(Bonus,MIN(SUMPRODUCT((B$2:B$21=B2)
/COUNTIF(B$2:B$21,B$2:B$21)),ROWS(Bonus)))
...

The range of bonus amounts also isn't necessary if there are 27 or
fewer bonus levels. Use CHOOSE and put the bonus amounts into the
formula. Here's a 3-level formula. Note that the second argument to MIN
must be one more than the number of levels, and the final argument to
CHOOSE must be 0.

C2:
=CHOOSE(MIN(SUMPRODUCT((B$2:B$21=B2)
/COUNTIF(B$2:B$21,B$2:B$21)),4),1000,500,250,0)


---
Message posted from http://www.ExcelForum.com/