How to find the row and column labels of the largest value in a table
Excel formula
=TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""))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,"")).
- Result on the example
- Ben in Mar in
G2 - Works in
- Excel 2019 and later, including Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""))Result"Ben in Mar"
B2:E54 rows × 4 cols: $12,400, $13,850, $11,900, $14,200, …A range: 4 rows × 4 columns.
MAX(B2:E5)16250MAX: Largest number.- number1
B2:E54 rows × 4 cols: $12,400, $13,850, $11,900, $14,200, …
- number1
B2:E5=MAX(B2:E5){4 rows × 4 cols} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
A2:A54 rows × 1 col: "Ana", "Ben", "Cara", "Dev"A range: 4 cells in one column.
A2:A5&" in "{4 rows × 1 col} "Ana in ", "Ben in ", "Cara in ", "Dev in "The & operator joins as text.
B1:E11 row × 4 cols: "Jan", "Feb", "Mar", "Apr"A range: 4 cells in one row.
A2:A5&" in "&B1:E1{4 rows × 4 cols} "Ana in Jan", "Ana in Feb", "Ana in Mar", "Ana in Apr", …The & operator joins as text.
IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""){4 rows × 4 cols} "", "", "", "", …IF: Returns one value if a test is true, another if false.- logical_test
B2:E5=MAX(B2:E5){4 rows × 4 cols} FALSE, FALSE, FALSE, FALSE, … - value_if_true
A2:A5&" in "&B1:E1{4 rows × 4 cols} "Ana in Jan", "Ana in Feb", "Ana in Mar", "Ana in Apr", … - value_if_false
""
- logical_test
TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""))"Ben in Mar"TEXTJOIN: Joins text with a delimiter between items.- delimiter
", " - ignore_empty
TRUE - text1
IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""){4 rows × 4 cols} "", "", "", "", …
- delimiter
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(B2:E5=MAX(B2:E5),A2:A5&" in "&B1:E1,""))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =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))) | Ben in Mar | 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.
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 row and column labels of the largest value in a table
G2, return which rep and which month had the highest sales in the table, as text like "Ana in Jan".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,"")).
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.