# How to calculate a value based on the row/col number of another cell value?

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.

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

## Code

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

output.push(["Name", "Number"]);

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

// return table
return output;
}
``````

## Screenshot

data result ## Example

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 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.)