# How to find the position of the largest value in Excel

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

**Excel:** `=XMATCH(MAX(B2:B13),B2:B13)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Month | Sales |  | Position of best month |
| Jan | 18200 |  |  |
| Feb | 17650 |  |  |
| Mar | 19800 |  |  |
| Apr | 21450 |  |  |
| May | 22900 |  |  |
| Jun | 24100 |  |  |
| Jul | 23300 |  |  |
| Aug | 22050 |  |  |
| Sep | 20700 |  |  |
| Oct | 21900 |  |  |
| Nov | 25350 |  |  |
| Dec | 24800 |  |  |

Formula in E1: `=XMATCH(MAX(B2:B13),B2:B13)`

Result: 11

## 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.

## Other ways

- `=MATCH(MAX(B2:B13),B2:B13,0)`: Works 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/position-of-largest-value