I have a range of names with their team names as headers, how do I retrieve the header for a specific persons name?


enter image description here

I have a person's name, and want to find the team they are associated with. how do I do this?

Let's say I have Name 40 using that, what would I need to do to determine the team it's under?


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:

  • The 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.

  • Thanks for the reply. I have tried this and just receive an error A circular dependency was detected on the arrayformula. When breaking it apart, I cannot actually perform a function like: A3:D50 = "Name 40", it always errors out saying An array value could not be found. I see what you are doing though, just need to figure out a way to do it where it doesn't error out. – Douglas Gaskell Oct 12 '15 at 1:12
  • Circular dependency means you put this formula within the range A3:D50. Either move it elsewhere or use a smaller range. – user79865 Oct 12 '15 at 1:14
  • The formula is outside of the range, It's in column F (or any other column I may put it in). The formula as a whole always returns Josh. If I break it down in an attempt to debug, the arrayformula chunk always errors saying A circular dependency was detected. If I break that down further, the A3:D50 = "Name 40" section always errors out with An array value could not be found regardless of the value. – Douglas Gaskell Oct 12 '15 at 1:22
  • The comparison of array to a string can't work without arrayformula, so that is not surprising. Try max(arrayformula(...)) instead of sum; this is a bit more robust. – user79865 Oct 12 '15 at 1:27
  • Oh sweet, after changing that it behaves as expected! Thanks a ton. – Douglas Gaskell Oct 12 '15 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.