Spreadsheet Practice

How to find the position of the largest value in Excel

Formula (Excel and Google Sheets)

=XMATCH(MAX(B2:B13),B2:B13)

Match the maximum within the range: =XMATCH(MAX(B2:B13),B2:B13) returns where it is, here month 11.

Result on the example
11 in E1
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

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

How it works

MAX finds the highest sales figure, $25,350. XMATCH looks for that figure in B2:B13 and returns its position, 11, which is November. Use the position with INDEX to fetch anything else from that row.

=XMATCH(MAX(B2:B13),B2:B13)

Result11

  1. B2:B1312 rows × 1 col: $18,200, $17,650, $19,800, $21,450, …

    A range: 12 cells in one column.

  2. MAX(B2:B13)25350

    MAX: Largest number.

    • number1B2:B1312 rows × 1 col: $18,200, $17,650, $19,800, $21,450, …
  3. XMATCH(MAX(B2:B13),B2:B13)11

    XMATCH: Modern MATCH: exact by default.

    • lookup_valueMAX(B2:B13)25350
    • lookup_arrayB2:B1312 rows × 1 col: $18,200, $17,650, $19,800, $21,450, …

In Google Sheets

The same formula works in Google Sheets: =XMATCH(MAX(B2:B13),B2:B13). We ran it in Google Sheets mode on the same data and it returns 11.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=MATCH(MAX(B2:B13),B2:B13,0)11Works in every version; the 0 asks for an exact match.

Common mistakes

  • With a tie for the maximum, the first position is returned. XMATCH(MAX(B2:B13),B2:B13,0,-1) returns the last one.
  • The position is inside the range. Add ROW(B2)-1 for the sheet row.

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 the largest value

In E1, return the position (1 to 12) of the month with the highest sales.

Questions

How do I return the month name instead?

Use INDEX with the position: =INDEX(A2:A13,XMATCH(MAX(B2:B13),B2:B13)).

How do I find the position of the smallest value?

Use MIN: =XMATCH(MIN(B2:B13),B2:B13).

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.