Find and Copy Excel Rows Matching a Value Across Multiple Columns
Question details
Find rows containing a specified value in any of 50 columns and copy the complete matching rows to another worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has approximately 82,000 records and 500 columns, but only 50 columns need to be searched.
- Observed behavior
- The desired output is a separate worksheet containing all columns from each matching row.
Identify the 50 search columns, the exact value to find, and the full data range. The examples assume headers in row 1, data in A2:SF82001, and 50 adjacent search columns in A:AX; adjust the ranges to match your worksheet.
Filter Matching Rows with a Helper Column
Use a helper column to identify matching rows, then copy their original columns to another worksheet.
On the source worksheet, enter the value to find in SH1, outside the 500-column data range. Do not leave the search cell blank.
Enter Match in SG1. In SG2, enter =COUNTIF(A2:AX2,$SH$1)>0, then fill the formula down through SG82001.
Select A1:SG82001 and choose Data > Filter. Open the filter arrow in SG1 and select TRUE.
Select A1:SF82001, choose Home > Find & Select > Go To Special > Visible cells only, and copy. On a separate worksheet, select A1 and paste.

Return Matching Rows with Excel FILTER and BYROW
Use a dynamic array formula when Excel supports FILTER, BYROW, and LAMBDA and you want results to update automatically.
Copy Matching Rows with WPS Spreadsheets
Use a COUNTIF helper column and a filter in WPS Spreadsheets to find values across the search columns and copy the matching records to a new sheet.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and identify the search columns and full data range.
- 2. Create a helper formula: For the example range A:SF, enter the search value in SH1 and Match in SG1. Enter =COUNTIF(A2:AX2,$SH$1)>0 in SG2 and fill it down to the last record.
- 3. Filter matching rows: Select A1:SG82001, choose Data > Filter, and use the SG1 filter to show TRUE values.
- 4. Copy to a new sheet: Select the filtered original data in A1:SF82001, copy it, and paste it into A1 of a new worksheet. Check that only the visible matching rows were pasted.

Frequently Asked Questions
Can I search nonadjacent columns?
Yes. In the helper formula, add COUNTIF results for each search range. For example, =COUNTIF(A2:J2,$SH$1)+COUNTIF(M2:AZ2,$SH$1)>0 checks both ranges.
Does the helper formula search for an exact value?
COUNTIF checks the cell against the criterion rather than looking for an arbitrary substring. Text matching is not case-sensitive, and * and ? act as wildcards unless preceded by ~.
Why does the FILTER formula show a spill error?
The output area may contain existing cells. Clear the cells below and to the right of the formula, then let Excel spill the results again.
What if my Excel version does not support BYROW?
Use the COUNTIF helper-column method and select Data > Filter to isolate matching rows. VBA or Power Query may also be useful for repeated processing.




