Random Cells From A List In Google Sheets

user_6790374 0
Viewed 845

Sample

Code:

=ArrayFormula((VLOOKUP(QUERY(UNIQUE(RANDBETWEEN(ROW(INDIRECT("A1:A"&COUNTA(A:A)*10))^0;COUNTA(A:A)));"limit 4");{ROW(INDIRECT("A1:A"&COUNTA(A:A)));FILTER(A:A;A:A<>"")};2;0)))

I'm trying but got this error,

test1 test 1

Can anyone tell what's wrong? To make my code to work as in the first picture

error in #REF! VLOOKUP evaluates outside the range bounds.

im try change code

=ArrayFormula((VLOOKUP(QUERY(UNIQUE(RANDBETWEEN(ROW(INDIRECT("A1:A"&COUNTA(A:A)*10))^0,COUNTA(A:A))),"limit 4"),{ROW(INDIRECT("A1:A"&COUNTA(A:A)));FILTER(A:A,A:A<>"")},1,0)))

but number

enter image description here

solution for that?

google-sheets-formula google-sheets
2 Answers
=ARRAYFORMULA(VLOOKUP(FLOOR(RANDARRAY(5)*COUNTA(A2:A)),{SEQUENCE(COUNTA(A2:A),1,0),A2:INDEX(A2:A,COUNTA(A2:A))},2))

  • Create 5 random integers using RANDARRAY between 0(inclusive) and number of entries in A2:A(i.e., COUNTA)(exclusive).

  • Create a artificial side by side array({arr1,arr2}) of SEQUENCE of numbers (from 0) and actual values in A2:A

  • VLOOKUP the random integers in the created artificial array to give random values in A:A

If you're in locales that use comma as decimal separators, The artificial array should be created using \ instead of ,({arr1\arr2}).

=ARRAYFORMULA(VLOOKUP(FLOOR(RANDARRAY(5)*COUNTA(A2:A));{SEQUENCE(COUNTA(A2:A);1;0)\A2:INDEX(A2:A;COUNTA(A2:A))};2))
user_84044530

On the first formula

{ROW(INDIRECT("A1:A"&COUNTA(A:A)));FILTER(A:A;A:A<>"")}

replace the semicolon ; between INDIRECT() and FILTER() by a backslash \ as using a semicolon appends the results of FILTER to the results of INDIRECT but you are looking to put the results of each function on their own column. Please note that this formula is using semicolons as argument separator.

On the second formula replace the semicolon ; between INDIRECT() and FILTER() by a comma , (and replace the third argument of VLOOKUP, 1, by 2. Please note that this formula is using commas as argument separators.

Explanation

  • Commas are used as argument separator on spreadsheets that use dot as decimal separator (=SUM(1,2,3)) but also use commas as columns separator on arrays ({"a","b"})
  • Semicolons are used as argument separator on spreadsheets that use comma as decimal separator (=SUM(1;2;3)). On these spreadsheets, backslashes are used as columns separator on arrays ({"a"\"b"});

References

user_15954510
Related