# How Do I filter this range of multiple rows and columns only by their headers?

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

Pic:

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