How to Ignore Blank cells while using "Randbetween" function

Hi, I just joined after searching for a solution to my problem. I am using MS Office Excel 365. I first started with this formula =INDEX(C4:C18,RANDBETWEEN(1,15)). When I have less than 15 names to choose from I sometimes get a random value of zero. I used an Array from another solution I found on this forum but on occasion the random value generated is zero instead of a name. I have entered the formula as an Array for the range I need. In my current example I have a maximum of 15 cells to randomly select a name but in this case I only have 8 names to randomly select, the rest are blank. The number of names can change from day to day from 4 to 15 names. My blank cells in my Array do contain formulas referencing another tab ie. ='Lookup Data'!H10 if that makes a difference.

This is my Array formula based on the solution provided in a previous post.
{=INDEX(C:C,INDEX(MODE.MULT(IF(C4:C18<>{"",""},ROW(C4:C18))),RANDBETWEEN(1,SUM(N(LEN(C4:C18)>0)))))}

Hopefully I have provided enough information.

Thank you for any help.

Dave

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.