A | B
1 Name | Weight
2 Bob | 1
3 Joe | 1
4 Sue | 5
5 Nick | 1
6 Ike | 1

I'm trying to setup a formula to return a random text string (values from column A) based on weighted values (column B). I'm trying to have a cell return Sue 5 times as often as the others.

I've been searching for a couple afternoons now to no avail. The closest I came was the following formula:
=INDEX(A:A,MATCH(RANDBETWEEN(0,MAX(C:C)),C:C,1))

from http://www.excelforum.com/excel-form...ml#post1678471

But this would return reference errors because at some point, it would try to select value 8, but there were only 5 values.

I'm also tried modifying randbetween functions with counta and various other things.

=LOOKUP(RANDBETWEEN(1,SUM(Values!$I$26:$I$32)),Val ues!$H$26:$H$32,Values!$J$26:$J$32)

=INDEX(J26:J36,MATCH(RANDBETWEEN(0,MAX(L26:L37)),L 26:L37,1))

Selecting a text value from a list at random is easy, this weighted part is giving me trouble.

Thanks for any guidance!