Function Problems

Excel Formula to Find an Exact Match and Return Its Row Number

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Find the worksheet row number of the first table row containing all required numbers.

Find an Exact Match and Return Its Row Number in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Compare several numbers against each row of a variable-sized table and identify the first row containing them.
Observed behavior
Complex array formulas and VBA can produce a result but may be slow on large tables.
Before you start

Identify the cells containing the required numbers and the actual table range. The example below uses four numeric criteria in B1:E1 and table data in I1:O10; adjust the references for your worksheet.

Solution 1Recommended

Use a helper column and MATCH to find the first matching row

Use this approach to check each row separately and avoid a large MMULT or AGGREGATE calculation.

The example checks whether each of the four numbers appears anywhere in a row. Their order does not matter, and the row may contain other values. Repeated criteria do not require repeated occurrences in the row.

A helper column can be easier to maintain, but its speed depends on the workbook and table size. Test it with your data.

1
Enter the criteria

Enter four numbers in B1:E1. Make sure all four cells contain numbers; the helper formula requires four numeric criteria.

2
Check the first table row

In P1, enter =AND(COUNT($B$1:$E$1)=4,COUNTIF(I1:O1,$B$1)>0,COUNTIF(I1:O1,$C$1)>0,COUNTIF(I1:O1,$D$1)>0,COUNTIF(I1:O1,$E$1)>0). This returns TRUE when the row contains all four criteria.

3
Fill the helper column

Select P1 and drag its fill handle down through P10, or through the last row of your table. Extend or shorten the range as the table changes.

4
Return the worksheet row number

In an empty cell, enter =IFERROR(MATCH(TRUE,P1:P10,0)+ROW(P1)-1,"No match"). MATCH finds the first TRUE, and ROW(P1) adjusts the result to the worksheet row number.

Use a helper column and MATCH to find the first matching row
Adjust the formula for your data: If you have a different number of criteria, change the COUNT check and add or remove COUNTIF checks. Reference only the rows and columns needed for the search.
Find matching rows in WPS Spreadsheets

Find the First Matching Row in WPS Spreadsheets

Use a helper column and MATCH in WPS Spreadsheets to identify the first row containing your required numbers.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use File > Open to select your workbook. Enter four required numbers in B1:E1.
  2. 2. Add a row check: With the table in I1:O10, enter =AND(COUNT($B$1:$E$1)=4,COUNTIF(I1:O1,$B$1)>0,COUNTIF(I1:O1,$C$1)>0,COUNTIF(I1:O1,$D$1)>0,COUNTIF(I1:O1,$E$1)>0) in P1.
  3. 3. Fill the helper column: Drag the fill handle from P1 down to P10, or to the last row of your table.
  4. 4. Find the first match: In an empty cell, enter =IFERROR(MATCH(TRUE,P1:P10,0)+ROW(P1)-1,"No match") to return the first matching worksheet row.
Check required numbers row by row with COUNTIFReturn the first matching worksheet row with MATCHOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface for free
microsoft office alternative - wps office

Frequently Asked Questions

Does the formula require the numbers to appear in a particular order?

No. Each COUNTIF checks the entire row range, so the numbers can appear in any order and the row can contain additional values.

What happens if no row contains all four numbers?

The MATCH formula returns "No match" through IFERROR. Check B1:E1 and the table range if you expected a result.

How do I search a table that starts below row 1?

Place the helper formula beside the first data row and update its row range. For example, if the helper range is P5:P100, use =IFERROR(MATCH(TRUE,P5:P100,0)+ROW(P5)-1,"No match").

How can I reduce calculation time on a large table?

Limit the COUNTIF and MATCH references to the actual table and helper-column ranges rather than entire columns. Test the helper-column approach against your existing formula, since performance varies by workbook.