# How to return the column header where a value is found in Excel

Return the header of every column where the value appears: `=TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,""))`.

**Excel:** `=TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,""))` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,"")))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Red Wolves | Blue Jays | Green Hornets | Gold Eagles |  | Player | Mason |
| Liam | Olivia | Noah | Emma |  | Team |  |
| Ava | Mason | Sophia | Lucas |  |  |  |
| Ethan | Mia | Logan | Harper |  |  |  |
| Zoe | Elijah | Amelia | James |  |  |  |
| Jack | Ella | Leo | Grace |  |  |  |

Formula in G2: `=TEXTJOIN(", ",TRUE,IF(A2:D6=G1,A1:D1,""))`

Result: Blue Jays

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

## Other ways

- `=INDEX(A1:D1,SUMPRODUCT((A2:D6=G1)*(COLUMN(A2:D6)-COLUMN(A1)+1)))`: 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.

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

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/header-where-value-is-found