Problem with Index / Randbetween formula

Question

Hi,

I am trying to figure out a formula that returns the number in a cell of a given array. I know the obvious answer is to use a VLOOKUP formula, but I am trying to make this random, via the RANDBETWEEN shown below.

This is what I already have:

 =INDEX('Array1'!E2:E100,RANDBETWEEN(2,100),1) 

This formula works most of the time, but in the array there are blank cells, which are returning a #REF error. 

The second obvious answer would be to fill or omit the blank cells. I would do that, but the E column referenced above is part of a larger array that prohibits me from omitting or filling the blank cells.

Any help on this would be greatly appreciated. Thank you in advance.

Answer 1

Hi, try

=INDEX(Array1!E2:E6,SMALL(IF(array1!E2:E6<>"",ROW(array1!E2:E6)-ROW(array1!E2)+1),RANDBETWEEN(1,COUNTIF(array1!E2:E6,"<>"))))

Answer 2

Looks like that formula worked!

Thank you, Ediardp

Answer 3

Hi,

I don't follow this. If there are blank cells in E2:E100 and your formula references one of those then the formula will return zero. If the blank cell contains a null string then the formula will return a blank so unless there is a #REF! error in E2:E100 and it gets referenced then I'm struggling to see from the information you've provide what it is you want to do.

Can you explain exactly how you end up with the formula returning a #REF! error and what you want instead?