Excel Office Scripts: Reference the First Matching Row and Update Column B
Question details
The user needs an Office Script that finds the first row in Sheet1 where column A matches the named cell ID on Sheet2 and updates that row's column B.
- Product
- Microsoft Excel Office Scripts
- Device & OS
- not provided
- Scenario
- An automation task must read a named ID from Sheet2, search Sheet1 column A, and copy the value from Sheet3!A1 into the matching row in column B.
- Observed behavior
- The required first-match lookup and column B update have not yet been automated with Office Scripts.
Confirm that the worksheets are named Sheet1, Sheet2, and Sheet3, and that the named cell ID exists on Sheet2. Make sure Sheet1 has data in column A and that Sheet3!A1 contains the value to write.
Use an Office Script to find the first matching row and update column B
Use this solution when the lookup and update must run automatically across Sheet1, Sheet2, and Sheet3.
The script reads the named ID from Sheet2, searches Sheet1 column A from top to bottom, and stops at the first exact match. It then writes the value from Sheet3!A1 to column B on that same row.
The comparison trims leading and trailing spaces and converts values to text. If no match is found, the script stops with an error instead of changing an unrelated row.
In Excel for the web, open the workbook, select the Automate tab, choose New Script, and replace the sample code with the script below.
Paste this code into the Office Scripts editor and select Run: const sheet1 = workbook.getWorksheet("Sheet1");\nconst sheet2 = workbook.getWorksheet("Sheet2");\nconst sheet3 = workbook.getWorksheet("Sheet3");\n\nconst idValue = String(sheet2.getRange("ID").getValue()).trim();\nconst sourceValue = sheet3.getRange("A1").getValue();\nconst usedRange = sheet1.getUsedRange();\n\nif (!usedRange) {\n throw new Error("Sheet1 does not contain data.");\n}\n\nconst firstUsedRow = usedRange.getRowIndex();\nconst rowCount = usedRange.getRowCount();\nconst columnAValues = sheet1.getRangeByIndexes(firstUsedRow, 0, rowCount, 1).getValues();\nconst matchIndex = columnAValues.findIndex(row => String(row[0]).trim() === idValue);\n\nif (matchIndex === -1) {\n throw new Error(`No match was found for ID: ${idValue}`);\n}\n\nconst targetRow = firstUsedRow + matchIndex;\nsheet1.getCell(targetRow, 1).setValue(sourceValue);
Return to Sheet1 and check the first row in column A matching the Sheet2 ID. Its column B cell should now contain the value from Sheet3!A1.
Move the workbook lookup workflow to WPS Office
WPS Office provides a free, lightweight spreadsheet environment with a familiar interface and strong Excel file compatibility. It is suitable for opening and editing the workbook while preserving common .xlsx formulas, layouts, and data, although Microsoft Office Scripts themselves must run in Excel for the web.
- 1. Open the workbook in WPS Spreadsheet: Install WPS Office, launch WPS Spreadsheet, select Open, and choose the Excel .xlsx workbook.
- 2. Review the source and target sheets: Open Sheet2 to check the ID value, Sheet1 to review column A and column B, and Sheet3 to confirm the source value in cell A1.
- 3. Run Office Scripts where supported: If the automation must use Office Scripts, open the saved workbook in Excel for the web and run the script from the Automate tab.
Frequently Asked Questions
How does the script identify the named ID on Sheet2?
The script uses sheet2.getRange("ID").getValue(), so the workbook must contain a named range called ID that points to the intended cell on Sheet2. If the name is workbook-scoped, confirm that it refers to the correct Sheet2 cell.
What happens when the ID appears more than once in Sheet1 column A?
The script uses findIndex, which returns the first matching row from top to bottom. Only that row's column B cell is updated.
Can the script copy a number, date, or formula result from Sheet3!A1?
Yes. getValue() reads the value from Sheet3!A1 and setValue() writes that value to Sheet1 column B. It copies the result rather than the source cell's formula or formatting.
Why does the script report that no match was found?
Check that the named ID contains the expected value and that Sheet1 column A uses the same text or number. The script trims surrounding spaces, but differences in spelling, punctuation, or other characters still prevent a match.
Can I use WPS Office to run this Office Script?
No. Office Scripts are a Microsoft Excel for the web automation feature. WPS Office can open and edit compatible .xlsx files, but the script must be created and run in an Office Scripts-supported version of Excel.
