Spreadsheet Practice

How to find the position of a value in a list in Excel

Formula (Excel and Google Sheets)

=MATCH(E1,A2:A11,0)

Use =MATCH(E1,A2:A11,0). It returns where the value sits in the range (1 for the first cell), and #N/A if it isn't there.

Result on the example
4 in E2
Works in
Every current version
Google Sheets
Same formula

See it work

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

How it works

MATCH looks down A2:A11 for the name in E1 and returns its position in that range, not a value from it. Maya Ortiz is the 4th name, so the result is 4. The 0 asks for an exact match in a list in any order.

=MATCH(E1,A2:A11,0)

Result4

  1. E1"Maya Ortiz"

    The value in E1.

  2. A2:A1110 rows × 1 col: "Sam Hollis", "Dana Brooks", "Eli Fischer", "Maya Ortiz", …

    A range: 10 cells in one column.

  3. MATCH(E1,A2:A11,0)4

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

    • lookup_valueE1"Maya Ortiz"
    • lookup_arrayA2:A1110 rows × 1 col: "Sam Hollis", "Dana Brooks", "Eli Fischer", "Maya Ortiz", …
    • match_type0

In Google Sheets

The same formula works in Google Sheets: =MATCH(E1,A2:A11,0). We ran it in Google Sheets mode on the same data and it returns 4.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XMATCH(E1,A2:A11)4Excel 2021, Microsoft 365 and Google Sheets. Exact match is the default, and a 4th argument of -1 finds the last occurrence instead of the first.

Common mistakes

  • The position is counted inside the range. If the list starts in row 2, position 4 is sheet row 5.
  • Without the 0, MATCH assumes a sorted list and does an approximate match, which gives wrong positions on unsorted names.
  • If the name appears twice, MATCH returns the first position. Use XMATCH(E1,A2:A11,0,-1) for the last.

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.

Find the position of a value in a list

The runners in A2:A11 are listed in finishing order. In E2, return the finishing place of the runner in E1.

Questions

How do I get the sheet row number instead?

Add the offset of the first row: =MATCH(E1,A2:A11,0)+ROW(A2)-1.

How do I return a message instead of #N/A?

Wrap it in IFNA: =IFNA(MATCH(E1,A2:A11,0),"Did not finish").

Can MATCH search a row?

Yes. Give it a horizontal range, such as =MATCH("Mar",B1:M1,0), to get a column position.

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.