📋 Find Matching Rows in Google Spreadsheets

1. Overview

\[ \begin{array}{l} \textbf{You can use Google Apps Script to find matching rows in a sheet --} \\ \text{based on specific column values or multiple criteria.} \\ \textbf{This method supports custom logic, exact or fuzzy matching --} \\ \text{and works on large datasets for reporting, lookups, or validation.} \end{array} \]

2. Use Cases

            
                - Lookup rows where a column matches a specific value
                - Find duplicate or repeated entries in large sheets
                - Match records across multiple sheets
                - Validate entries based on rules (e.g., "status" is "Approved" and "score" > 80)
            
        

Matching rows helps automate filtering, reporting, or error-checking processes.

3. Script to Find Rows Matching a Value

            
                function findMatchingRows() {
                  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
                  const data = sheet.getDataRange().getValues();
                  const header = data[0];
                  const targetColumn = header.indexOf("Status");
                  const matches = [];

                  for (let i = 1; i < data.length; i++) {
                    if (data[i][targetColumn] === "Approved") {
                      matches.push(data[i]);
                    }
                  }

                  Logger.log(matches);
                  return matches;
                }
            
        

This function finds and logs all rows where the **Status** column equals "Approved".

4. Script to Find Matching Rows Across Two Sheets

            
                function findCrossSheetMatches() {
                  const ss = SpreadsheetApp.getActiveSpreadsheet();
                  const sheet1 = ss.getSheetByName("Sheet1");
                  const sheet2 = ss.getSheetByName("Sheet2");
                  
                  const data1 = sheet1.getDataRange().getValues();
                  const data2 = sheet2.getDataRange().getValues();
                  const index1 = data1[0].indexOf("Email");
                  const index2 = data2[0].indexOf("Email");
                  
                  const emails2 = data2.slice(1).map(row => row[index2]);
                  const matches = [];

                  for (let i = 1; i < data1.length; i++) {
                    if (emails2.includes(data1[i][index1])) {
                      matches.push(data1[i]);
                    }
                  }

                  Logger.log(matches);
                  return matches;
                }
            
        

This code finds rows in `Sheet1` whose **Email** also exists in `Sheet2`.

5. Script to Find Duplicates in a Single Column

            
                function findDuplicates() {
                  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
                  const data = sheet.getDataRange().getValues();
                  const colIndex = data[0].indexOf("ID");
                  
                  const seen = {};
                  const duplicates = [];

                  for (let i = 1; i < data.length; i++) {
                    const value = data[i][colIndex];
                    if (seen[value]) {
                      duplicates.push(data[i]);
                    } else {
                      seen[value] = true;
                    }
                  }

                  Logger.log(duplicates);
                  return duplicates;
                }
            
        

This method identifies repeated entries in the **ID** column.

6. Optional: Output Matches to a New Sheet

            
                function outputMatchesToSheet(matches) {
                  const ss = SpreadsheetApp.getActiveSpreadsheet();
                  let target = ss.getSheetByName("Matches");
                  if (!target) {
                    target = ss.insertSheet("Matches");
                  } else {
                    target.clearContents();
                  }
                  target.getRange(1, 1, matches.length, matches[0].length).setValues(matches);
                }
            
        

Use this helper function to write your matching rows into a separate tab for viewing or export.

7. Conclusion

            
                Google Apps Script makes it easy to search and filter matching rows based on 
                any logic you define. This helps automate validation, reporting, and data integration workflows.
            
        

With added enhancements like conditional logic, logging, and sheet-to-sheet comparison, it becomes a powerful backend for your spreadsheet operations.