Getting the row and/or the column ID is important because it will allow to define the range to perform different actions. The “getRowIndex” and “getColumnIndex“ functions allow to do it but remember that the script defines row 1 = row 0 and column 1 (which is A) = column 0 so in the code below, I will add “+ 1” after the function. For the column, I also add a code line to get the letter so just choose which one you need, the ID or the letter.

Row ID	Column ID

 

When I use the script ?

To find the column or the row ID based on value to be used for a range.

 

How to create the script ?

Read How to create, edit and select an Office Script in an excel report

 

How to create the button to associate it with the script ?

Read How to create a button and associated it to an Office Script in an excel report

 

How is/are the script(s) ?

Copy the code below and paste it into your script. You will see my comments in green if exist so follow the help to adapt to your need.

For row ID (from top to down):

function main(workbook: ExcelScript.Workbook) {
    // change column range D2:D5 by yours
    // change oracle by yours
    // remove + 1 to get ID as defined by script
    let rowid = workbook.getActiveWorksheet().getRange("D2:D5").find("oracle", { completeMatch: false, matchCase: false, }).getRowIndex() + 1;
    console.log(rowid)
}              
              

For row ID (from down to top):

function main(workbook: ExcelScript.Workbook) {
    // change column range D2:D5 by yours
    const range = workbook.getActiveWorksheet().getRange("D2:D5");
    const values = range.getValues();
    // to search from top to down (let i = 0; i < values.length; i++)
    for (let i = values.length - 1; i >= 0; i--) {
        const cellValue = values[i][0];
        // change oracle by yours
        // to search into a sentence toLowerCase().includes("oracle")
        if (typeof cellValue === "string" && cellValue.toLowerCase() === "oracle") {
            // remove + 1 to get ID as defined by script
            let rowid = range.getCell(i, 0).getRowIndex() + 1;
            console.log(rowid);
            return;
        }
    }
}               
               

Get Row ID for each cell found:

function main(workbook: ExcelScript.Workbook) {
    // change column range D2:D5 by yours
    const values = workbook.getActiveWorksheet().getRange("D2:D5").getValues();
    for (let i = 0; i < values.length; i++) {
        // 2 because D2
	const rowid = i + 2;
        const cellValue = values[i][0];
        // change oracle by yours
	// to search into a sentence toLowerCase().includes("oracle")
        if (typeof cellValue === "string" && cellValue.toLowerCase() === "oracle") {
            // change column D by yours
	    console.log(`D${rowid}`);
        }
    }
}             
              

For column ID:

function main(workbook: ExcelScript.Workbook) {
    // change row range 3:3 by yours
    // change close by yours
    // remove + 1 to get ID as defined by script
    let columnid = workbook.getActiveWorksheet().getRange("3:3").find("close", { completeMatch: false, matchCase: false, }).getColumnIndex() + 1;
    // split("3") corresponds to row 3
    let columnletter = workbook.getActiveWorksheet().getRange("3:3").find("close", { completeMatch: false, matchCase: false, }).getAddress().split("3")[0];
    console.log(columnid)
    console.log(columnletter)
}