Count Exact Row Matches and Return Row Numbers in Excel
Question details
Find rows containing exactly the required number of matching values and return their row numbers.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to compare values in each table row against a set of specified numbers and identify rows with exactly four matches.
- Observed behavior
- Only rows meeting the specified match count should return a row number; rows with fewer or more matches should be excluded.
Decide whether matches are based on distinct values, cell positions, or partial text, and identify the ranges to compare. The example below counts distinct numeric targets found anywhere in each row.
Use COUNTIF to find rows with exactly four matching targets
Use this formula when each row must match exactly four of five distinct numbers, regardless of their positions.
This example checks whether each of five targets appears at least once in a row. Repeated occurrences of the same target count as one match, and values outside the target list do not count.
Place the data to check in columns A through F, starting in row 2. Enter five distinct, nonblank numeric targets in H2:H6.
In G2, enter =IF(SUM(--(COUNTIF(A2:F2,$H$2)>0),--(COUNTIF(A2:F2,$H$3)>0),--(COUNTIF(A2:F2,$H$4)>0),--(COUNTIF(A2:F2,$H$5)>0),--(COUNTIF(A2:F2,$H$6)>0))=4,ROW(),"").
Drag the fill handle from G2 down alongside the data. Column G displays the worksheet row number when exactly four targets are present; otherwise, it displays a blank.
Count exact row matches in WPS Spreadsheet
Use WPS Spreadsheet to compare each row with a numeric target list and return row numbers when exactly four distinct targets match.
- 1. Open the spreadsheet: Open WPS Spreadsheet and select Open to load your workbook.
- 2. Set up the ranges: Place the row data in columns A–F and enter five distinct numeric targets in H2:H6.
- 3. Apply the formula: Enter the COUNTIF formula from the solution above in G2, then drag the fill handle down to check each row.
- 4. Save the results: Select Menu > Save As and choose an Excel-compatible format such as XLSX.
Frequently Asked Questions
Does a repeated number count as more than one match?
No. The formula checks whether each target appears at least once, so repeated occurrences of one target count as one match.
What happens if a row contains all five targets?
The formula returns a blank because its match count is five, not exactly four.
Can I use only four target numbers?
Yes, but the formula must be adjusted to check four target cells. It will identify rows containing all four targets, even if those rows also contain other values outside the target list.
Can this formula find partial text matches?
The example is intended for exact numeric targets. Partial text matching requires different criteria, and the formula should be adapted to the text and matching rules.
