Spreadsheet Practice

How to return the row number of a matching value in Excel

Formula (Excel and Google Sheets)

=MATCH(F1,A2:A9,0)+ROW(A2)-1

Add the first row's offset to MATCH: =MATCH(F1,A2:A9,0)+ROW(A2)-1 gives the sheet row of the match.

Result on the example
6 in F2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.

How it works

MATCH returns the position inside A2:A9: INV-1047 is 5th. The range starts in row 2, so ROW(A2)-1 adds the one row above it and the result is sheet row 6. Writing the offset with ROW keeps the formula right if the list moves.

=MATCH(F1,A2:A9,0)+ROW(A2)-1

Result6

  1. F1"INV-1047"

    The value in F1.

  2. A2:A98 rows × 1 col: "INV-1043", "INV-1044", "INV-1045", "INV-1046", …

    A range: 8 cells in one column.

  3. MATCH(F1,A2:A9,0)5

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueF1"INV-1047"
    • lookup_arrayA2:A98 rows × 1 col: "INV-1043", "INV-1044", "INV-1045", "INV-1046", …
    • match_type0
  4. A2"INV-1043"

    The value in A2.

  5. ROW(A2)2

    ROW: Row number of a reference, or of this cell.

    • referenceA2"INV-1043"
  6. MATCH(F1,A2:A9,0)+ROW(A2)7

    The + operator adds.

  7. MATCH(F1,A2:A9,0)+ROW(A2)-16

    The - operator subtracts.

In Google Sheets

The same formula works in Google Sheets: =MATCH(F1,A2:A9,0)+ROW(A2)-1. We ran it in Google Sheets mode on the same data and it returns 6.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=MATCH(F1,A:A,0)6Searching the whole column gives the sheet row directly, as long as nothing above the list could match.
=XLOOKUP(F1,A2:A9,ROW(A2:A9))6Returns the row from a list of row numbers. Excel 2021, Microsoft 365 and Google Sheets.

Common mistakes

  • MATCH on its own gives the position in the range, not the row. Forgetting the offset is off by one for every header row.
  • With duplicates you get the first row. Use XMATCH(F1,A2:A9,0,-1)+ROW(A2)-1 for the last one.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Return the row number of a matching value

In F2, return the sheet row number where the invoice in F1 appears.

Questions

How do I get the row number for two criteria?

Match 1 against multiplied tests: =MATCH(1,(A2:A9=F1)*(B2:B9="Adatum"),0)+ROW(A2)-1.

How do I get every row number that matches?

Filter the row numbers: =FILTER(ROW(A2:A9),B2:B9="Contoso").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.