How to return the column header where a value is found in Excel
Excel formula
=TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,""))Return the header of every column where the value appears: =TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,"")).
- Result on the example
- Blue Jays 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:D6=G1 compares every cell of the rosters with the name. IF returns the header of the column wherever there is a match, because the one-row header range is repeated down the table, and "" everywhere else. TEXTJOIN joins what is left, so a player on two teams gets both.
=TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,""))Result"Blue Jays"
A2:D65 rows × 4 cols: "Liam", "Olivia", "Noah", "Emma", …A range: 5 rows × 4 columns.
G1"Mason"The value in G1.
A2:D6=G1{5 rows × 4 cols} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
A1:D11 row × 4 cols: "Red Wolves", "Blue Jays", "Green Hornets", "Gold Eagles"A range: 4 cells in one row.
IF(A2:D6=G1,A1:D1,""){5 rows × 4 cols} "", "", "", "", …IF: Returns one value if a test is true, another if false.- logical_test
A2:D6=G1{5 rows × 4 cols} FALSE, FALSE, FALSE, FALSE, … - value_if_true
A1:D11 row × 4 cols: "Red Wolves", "Blue Jays", "Green Hornets", "Gold Eagles" - value_if_false
""
- logical_test
TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,""))"Blue Jays"TEXTJOIN: Joins text with a delimiter between items.- delimiter
", " - ignore_empty
TRUE - text1
IF(A2:D6=G1,A1:D1,""){5 rows × 4 cols} "", "", "", "", …
- delimiter
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,""))). 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(A1:D1,SUMPRODUCT((A2:D6=G1)*(COLUMN(A2:D6)-COLUMN(A1)+1))) | Blue Jays | Works in every version without Ctrl+Shift+Enter, when the name appears once: SUMPRODUCT returns its column number. |
Common mistakes
- The header range must be as wide as the table and start in the same column.
- The SUMPRODUCT version adds up column numbers, so a name in two columns returns a wrong team. TEXTJOIN lists both.
- In Excel 2019, confirm the formula with Ctrl+Shift+Enter. In Google Sheets, IF only works across a range inside ARRAYFORMULA, as in the Sheets version.
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.
Return the column header where a value is found
G2, return the team (the header) of the player named in G1.Questions
How do I return the row instead of the column?
Use row labels in the IF, or the row number: =SUMPRODUCT((A2:D6=G1)*ROW(A2:D6)).
How do I check whether the name is on any team?
Count it: =COUNTIF(A2:D6,G1)>0.
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.