I have a Google Sheet formatted like this:

I have a Google Sheet formatted like this:

I'd like to create a formula that picks a random cell from the Name column, but only if it matches a specific status in the Status column.

Currently I sort the data in a way that the desired statuses are grouped together and use the formula =INDEX(games!A2:A626,RANDBETWEEN(1,625)). But, I'd like to be able to sort the data in any way without breaking the intended result.

• google-sheets
• random
• google-sheets-formula

• 1 Can you try =INDEX(FILTER(A2:A, B2:B="Status"), RANDBETWEEN(1, COUNTA(FILTER(A2:A, B2:B="Status")))) to see if that works? – Saddles Commented Feb 15 at 14:27
• @Saddles I tried =INDEX(FILTER(games!A2:A1090,games!B2:B1090="Backlog"),RANDBETWEEN(1,COUNTA(FILTER(A2:A1090,B2:B1090="Backlog")))) and it's just returning the first entry [Redacted]. – Juice ♦ Commented Feb 15 at 22:53
• I tried the formula you provided, and it seems to be working properly. Can you share something reproducible that shows it behaving that way? Consider testing it out on a new spreadsheet as well by going to sheets.new. – Saddles Commented Feb 15 at 23:06
• Another formula that yields the expected result is =LET(a, QUERY(A2:D, "SELECT Col1 WHERE Col2 = 'Status'"), INDEX(a, RANDBETWEEN(1, ROWS(a)))). As a last resort in sharing the replicable data, you may use what the Google Product Experts created, which is the Make an anonymous sample document. – Saddles Commented Feb 15 at 23:50
• I'll post an answer with an alternative for when the same issue persists. I hope it works! – Saddles Commented Feb 17 at 1:35

1 Answer 1

You may try:

=INDEX(FILTER(A2:A, B2:B="Backlog"), RANDBETWEEN(1, COUNTA(FILTER(A2:A, B2:B="Backlog"))))

Where FILTER(A2:A, B2:B="Status") is used to get the specific status in the Status column without having to sort the data, and COUNTA(FILTER(A2:A, B2:B="Status")) to return its count for RANDBETWEEN.

The formula can also be written as:

=LET(a, FILTER(A2:A, B2:B="Backlog"), INDEX(a, RANDBETWEEN(1, COUNTA(a))))

You may also try this with RAND and CEILING:

=LET(a, FILTER(A2:A, B2:B="Backlog"), INDEX(a, CEILING(RAND() * ROWS(a))))

Another option is to use the QUERY function:

=LET(a, QUERY(A2:D, "SELECT Col1 WHERE Col2 = 'Backlog'"), INDEX(a, RANDBETWEEN(1, ROWS(a))))

=LET(a, QUERY(A2:D, "SELECT Col1 WHERE Col2 = 'Backlog'"), INDEX(a, CEILING(RAND() * ROWS(a))))

To return the entire row, you may also try these:

=LET(a, FILTER(A2:D, B2:B="Backlog"), INDEX(a, RANDBETWEEN(1, ROWS(a))))

=LET(a, QUERY(A2:D, "SELECT * WHERE Col2 = 'Backlog'"), INDEX(a, RANDBETWEEN(1, ROWS(a))))

=LET(a, QUERY(A2:D, "SELECT * WHERE Col2 = 'Backlog'"), INDEX(a, CEILING(RAND() * ROWS(a))))

Alternatively, if the formulas are just returning the first entry [Redacted], you may try this custom function that should achieve what you'd like:

const myFunction = () => {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Games");
  var vl = ss.getRange(2, 1, ss.getLastRow() - 1, 4).getValues();
  var fd = vl.filter(r => r[1] == "Backlog");
  var op = fd[Math.floor(Math.random() * fd.length)];
  return op[0];
}

Note: Change op[0] to [op] to return the entire row. Call this in a cell with =myFunction() after pasting the code in Google Apps Script. You may do that by selecting Extensions > Apps Script from the toolbar of the Google Sheet.

If everything's in order and the issue persists, I recommend that you consider submitting a bug report to let Google know about the unusual behavior that's going on.

REFERENCES

• FILTER function
• Custom Functions in Google Sheets