Is it possible to Index a single cell array?

JonDalton

New Member
Joined
Nov 12, 2018
Messages
9
I was trying to randomly select items from an array, I don't think the below is possible so I'm going to break it out into cells.

I have an array in A1, everything below is submitted with ctrl shift enter just in case.

A1={"test1","test2","test3"}

I want to be able to:
=INDEX(A1,RANDBETWEEN(1,3))

Results from =INDEX,A1,RANDBETWEEN(Row,Column))

RowColumnResult
00
test1

<tbody>
</tbody>
01
test1

<tbody>
</tbody>
02
#REF!

<tbody>
</tbody>
10
test1

<tbody>
</tbody>
11
test1

<tbody>
</tbody>
12
#REF!

<tbody>
</tbody>
20
#REF!

<tbody>
</tbody>
21
#REF!

<tbody>
</tbody>
22
#REF!

<tbody>
</tbody>
30
#REF!

<tbody>
</tbody>
31
#REF!

<tbody>
</tbody>
32
#REF!

<tbody>
</tbody>

<tbody>
</tbody>

Is this possible with a different function?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,110
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, you can't do that. You could put the array into a defined name instead.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,110
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I suspected as much. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,892
Messages
5,598,702
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top