Data Import & Export

Find and Copy Excel Rows Matching a Value Across Multiple Columns

John WilsonJohn Wilson Nov 14, 2026 868 views

Question details

Find rows containing a specified value in any of 50 columns and copy the complete matching rows to another worksheet.

Find and Copy Excel Rows Matching a Value Across Multiple Columns
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.
Before you start

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.

Solution 1Recommended

Filter Matching Rows with a Helper Column

Use a helper column to identify matching rows, then copy their original columns to another worksheet.

1
Enter the search value

On the source worksheet, enter the value to find in SH1, outside the 500-column data range. Do not leave the search cell blank.

2
Add the match formula

Enter Match in SG1. In SG2, enter =COUNTIF(A2:AX2,$SH$1)>0, then fill the formula down through SG82001.

3
Filter the results

Select A1:SG82001 and choose Data > Filter. Open the filter arrow in SG1 and select TRUE.

4
Copy the complete rows

Select A1:SF82001, choose Home > Find & Select > Go To Special > Visible cells only, and copy. On a separate worksheet, select A1 and paste.

Filter Matching Rows with a Helper Column
Search range and criteria: The formula searches adjacent columns A:AX. For nonadjacent columns, add COUNTIF results for the relevant ranges. COUNTIF is not case-sensitive and treats * and ? as wildcards; prefix them with ~ to search for literal characters.
Search and filter in WPS Spreadsheets

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. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheets and identify the search columns and full data range.
  2. 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. 3. Filter matching rows: Select A1:SG82001, choose Data > Filter, and use the SG1 filter to show TRUE values.
  4. 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.
Find matches across 50 adjacent columns with one helper formula per rowKeep all 500 original columns in the copied resultsOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and filters
microsoft office alternative - wps office

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.