CODEWITHSUNDEEP
google-sheets
Alex Nieh
Alex Nieh

Reputation: 73

Return the heading of the column in which a cell contains a given word

Setup: Row 1: Headings

Row 2: Names where each cell contains several names.

Problem: I want to check if row n contains "Tom" and return the corresponding heading. I don't want to use helping cells since I have to do this for a huge amount of names.

I tried query and hlookup. Further i tried to use regex together with an arrayformuar. But Nothing worked for me.

Upvotes: 0

Views: 28

Answers (1)

user6655984
user6655984

Reputation:

For example, 

=filter(A$1:F$1, regexmatch(A2:F2, "Joe"))

returns any headings where the content of the 2nd row contains "Joe", case-sensitive.

The following subtly different formula (with + before filter) returns only the first such heading:

=+filter(A$1:F$1, regexmatch(A2:F2, "Joe"))

Upvotes: 2

Related Questions