I am trying to build a spreadsheet where one sheet holds a list of names divided into groups and a second sheet should list all the names, sorted, with a number next to each name according to a calculation based on the first sheet.

Please see demo spreadsheet for a demo spreadsheet with the desired state.


I would go about solving it a bit differently. Advantage is that this little code will prepare the table at once.


function tournament(range) {
  // create output array
  var output = [];

  // add header names
  output.push(["Name", "Number"]);

  // create table
  for(var i=2, iLen=range.length; i<iLen; i++) {
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      // push letter, ring number * 100 + tournament into array
      output.push([range[i][j], (range[0][j] * 100) + (i-1)]);

  // return table
  return output;


enter image description here

enter image description here


I've created an example file for you: Tournament


I would use a script to solve this problem. It is possible to use the MATCH-function in combination with INDEX to solve it without scripting but it doesn't scale very well and demands much more work if you make any changes to your table. Read more here.

Here's my script with comments that hopefully makes it understandable:

function calcNum(value) { //Function with the value to search for as parameter 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var rangeRow = 4;  //First row of range to search in
  var rangeCol = 2;  //First column if range to search in
  var rangeRows = 3; //Number of rows of range to search in
  var rangeCols = 6; //Number of columns of range to search in

  var range = sheet.getRange(rangeRow, rangeCol, rangeRows, rangeCols); //Saves the range to a variable
  var values = range.getValues(); //Saves values in range to an array

  var valRow; //Defines variable for row of value to search for
  var valCol; //Defines variable for column of value to search for

  var ringNum; //Defines variable for ring number
  var tourNum; //Defines variable for tournament number

  for(var i = 0; i < rangeRows;i++){ //Loop to look through all rows in range
    for(var j = 0; j < rangeCols; j++){ //Loop to look through columns in current row
      if(values[i][j] == value){ //If current cell from loops is equal to value (that we're searching for)
        valRow = i + rangeRow; //Row of found value
        valCol = j + rangeCol; //Column of found value
  ringNum = sheet.getRange(2, valCol).getValue(); //Gets ring number (value of cell in row two, column from found value)
  tourNum = sheet.getRange(valRow, 1).getValue(); //Gets tournament number (value of cell in row from found value, column 1)

  return ringNum * 100 + tourNum; //Returns Your value

So in cell C11 you write =calcNum(B11), in C12 you write =calcNum(B12) and so forth. (You only have to enter it in the first cell, and then copy down by selecting the cell and dragging the square in the bottom right corner down.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.