# "Parameter X Value Y is out of range" being returned by INDEX combined with RANDBETWEEN

I'm making a random team selector but I'm getting a `#NUM!` error saying that "Parameter 2 Value 20" or "Parameter 2 Value 25" is out of range. From what I understand, they are not.

The parameters referred by the function are in other sheets.. Here is the link to it. Example:

Do you think this is a bug or am I doing something wrong?

I see commands like

``````=index(Spain!A\$2:A\$19, randbetween(2,19))
``````

The second parameter of `index` is not the absolute row number but is relative to the range specified as the first argument. For example,

• `=index(A\$2:A\$19, 1)` refers to A2
• `=index(A\$2:A\$19, 18)` refers to A19
• `=index(A\$2:A\$19, 19)` throws an error

To fix this, either change the range of `randbetween`:

``````=index(Spain!A\$2:A\$19, randbetween(1,18))
``````

or, if you want to refer by absolute row numbers, use `indirect`:

``````=indirect("Spain!A"&randbetween(2,19))
``````
• That's a nice and elegant solution. Thank you very much. I still didn't quite get why index combined with randbetween didn't work, I do understand that the function didn't quite work for what I intended though. Any chance of you giving another explanation for me? (I might research later more about it anyway, but if you have one, I'll be thankful). Sep 15, 2015 at 1:43
• Index with randbetween can work, if you give correct bounds for randbetween. The second argument of index is row number relative to the range you gave as the first argument. I gave an example of how your command `=index(Spain!A\$2:A\$19, randbetween(2,19))` can throw an error: it's when the random number happens to be 19.
– user79865
Sep 15, 2015 at 2:22
• Mm, yeah, the relative was (and still is) confusing me, I made a test as the following: =index(England!A\$2:A\$21, randbetween(1:19)) and it works perfectly, randomizing between from the first (AFC Bournemouth) to the last one (West Ham), I thought it would show the name of the league instead (since it's on A1) and ignore West Ham, because it is out of the range between 1 and 19, but I think that's where relative plays its part. Thank you for your help and patience teaching me this. :) Sep 15, 2015 at 4:10