Pull values randomly from a column (not a row)

[MSC]

Hi, I think this is pretty simple but I can't figure it out after many minutes of Googling here and other sites, thank you in advance for your help.

I have a long list of values in one row and I've successfully created a cell out to the side that randomly picks one value, using =index(B3:B184,randbetween(1,rows(B3:B184)),1).

That works great.

What I can't do is create the same thing to pull from a column of values. I've tried using the above formula but changing the cell reference to D2:J2, and then changing "rows" to "columns" but that returns #REF! despite there being straight-up text values in cell D2, J2, and everything in between.

What would you suggest?
Thanks,
Mike

 

[Fluff]

If you are trying to pull from a single column why are you using D:J2 which is a single row?

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MSC]

Today I'm on my work PC, which is Excel for Office Home/Business 2019, version 2104, build 13929.20386.

Sorry for the rows/columns confusion, my confusion stems in part from the fact that my first formula - containing the word 'rows' - works to pull data housed solely in columns (column B).

So I now want to find a way to get data housed in one row (row 2), which seems like it would requires the word 'columns' in the formula somewhere. Thank you.

 

[Fluff]

Ok, how about

=INDEX(D2:J2,RANDBETWEEN(1,COLUMNS(D2:J2)))

 

[MSC]

Bingo, getting rid of the
,1
at the end worked - thank you for your help.

 

[Fluff]

You're welcome & thanks for the feedback.