Get vlookup to select below on sorted list

[Blymurkla]

I’m following this Excel guide to use vlookup and randbetween to select a random cell from a sorted list. Unfortunately, either the guide is flawed or there’s a discrepancy between Excel and LibreOffice Calc. Anyway, I could use some help.

I’m using this function:

=VLOOKUP(RANDBETWEEN(1;100);A:C;3;TRUE())

It draws a random number between 1 and 100 and looks in column A for the matching number. Because it’s a sorted list and I’ve set the function to TRUE it will always return a number, even if it can’t find an exact match.

What I need (and what the guide says) is that the function draws the next closest match below. I.e., if it draws ‘23’ and goes along the list ‘19, 21, 22, 27, 29’ it will take 27. What it currently does is it pulls up the next closest match above.

Is there a way around this?

[karolus]

Try simply: =INDEX(C1:C?;RANDBETWEEN(1;?))
replace the 2 Questionmarks ? by your upper limit.

btw. sorted Vlookup pulls the next lower match, the behavior you stated is false.

##Edit:

After taking a closer look into your linked Excel-guide I realize: this guide is wrong!!

For proper results you should start with 0 (zero) in A1, and continue in A2 with Formula:

#=SUM(B$1:B1)

fill down to Cell A5001

For the random pick weighted by population you need:

#=VLOOKUP(RANDBETWEEN(0;$A$5001);A$1:C$5000;3)

###see attached file:
weighted-random-results.ods