Problem with INDEX and RANDBETWEEN

[tpmcmahon]

I am trying to sample randomly from a column of numbers using a combination of the INDEX and RANDBETWEEN functions. The formula was "=INDEX($C$15:$C$213,RANDBETWEEN(15,213)" - this was failing on a regular basis, returning "#REF!". I separated the two statements, to try to find where the error was occurring, and discovered that any time the RANDBETWEEN function returned a number >=200, the INDEX function returned that error. I shifted the data up so that the values were in rows 2 to 200 - same issue. Got rid of the header so they were in 1-199 and no problem. Has anyone else run into this? Is there a way around it? I would like to use this on a data set that exceeds 200 rows. I am using Office Home & Business 2016 and I last updated earlier today.

[#2]

@tpmcmahon , thats correct behaviour. You have 199 cells in you range starting from C15. Thus INDEX(C15:C213,1) returns C15; INDEX(C15:C213,199) returns C213 and INDEX(C15:C213,200) returns error since you out of range

[#3]

Thanls, @Sergei Baklan!  I must of had a brain cramp today - I don't know why I kept trying to set to worksheet row number rather than array row number.

[#4]

@tpmcmahon , no problem, happens with all of us. Glad to help.