# How to find the row and column labels of the largest value in a table

Compare every cell with the maximum and join the row and column labels where it matches: `=TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""))`.

**Excel:** `=TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""))` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,"")))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Rep | Jan | Feb | Mar | Apr |  | Best month |
| Ana | 12400 | 13850 | 11900 | 14200 |  |  |
| Ben | 15100 | 14600 | 16250 | 15800 |  |  |
| Cara | 9800 | 11250 | 12700 | 13100 |  |  |
| Dev | 13300 | 12900 | 14100 | 13650 |  |  |

Formula in G2: `=TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""))`

Result: Ben in Mar

## How it works

`A2:A5&" in "&B1:E1` builds a label for every cell of the table, because the column of names and the row of months are combined across the whole grid. IF keeps the label only where the cell equals the maximum, $16,250, and TEXTJOIN joins what is left. If two cells tie, both labels appear.

## Other ways

- `=INDEX(A2:A5,SUMPRODUCT((B2:E5=MAX(B2:E5))*(ROW(B2:E5)-ROW(B2)+1)))&" in "&INDEX(B1:E1,SUMPRODUCT((B2:E5=MAX(B2:E5))*(COLUMN(B2:E5)-COLUMN(B2)+1)))`: Works in every Excel version when the maximum appears once: each SUMPRODUCT returns the position of the top cell.

## Common mistakes

- The name range must be a column as tall as the table, and the month range a row as wide as it.
- The SUMPRODUCT version adds positions, so a tie for the maximum gives a wrong label.
- In Excel 2019, confirm the TEXTJOIN version with Ctrl+Shift+Enter.

## Questions

**How do I find the smallest value's labels?**

Use MIN in place of MAX.

**How do I return only the month?**

Leave out the names: `=TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),B1:E1,""))`.

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/labels-of-largest-value-in-table