An obvious issue is that a name may not be unique. Below I assume that it is; otherwise my formula won't give a meaningful result, and you will want to use a better identifier than a name.
I will use an
offset from the cell with "Team 1". In the example, this cell is A2 but the formula is written to be adaptable to other ranges.
=offset(A2, 0, max(arrayformula(if(A3:D50 = "Name 40", column(A2:D)-column(A2), 0))))
This formula returns "Team 4". Explanation:
if statement evaluates to 0 at the cells that do not have the needed name. At the cell with this name, it is equal to the column number minus the column number of A2.
- After taking the maximum, we just have the single number mentioned above
- This number is precisely what is needed for
offset(A2,0,...) to return the team name.
There are other approaches: e.g., join each column into a regular expression
^...|...|...|...$ and test the name against each.