How do I pick a random cell in Google Sheets based on the cell next to the target list of data?

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.

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