Search Across Columns and Return the Header in Google Sheets

Have you ever wondered how to efficiently search for a keyword across multiple columns and return the corresponding column header in Google Sheets? Look no further! In this tutorial, I’ll show you a dynamic formula that will make this process a breeze, even with a large number of columns.

Example to Search Across Columns and Return the Header

Let’s start with a practical example. Imagine you have a spreadsheet with a search key in cell B2, and you want to search for this key in columns C, D, and E. Here’s how it works:

Since we only have three columns to search, we can use the Match formula. Here’s the formula you can use:

=ifs(isna(match(B2,C3:C12,0))=FALSE,C2,isna(match(B2,D3:D12,0))=FALSE,D2,isna(match(B2,E3:E12,0))=FALSE,E2)

This formula searches for the keyword in the specified columns and returns the header of the column where it’s found. It’s like magic!

But what if you have a larger number of columns? This is where the dynamic formula comes into play.

Dynamic Formula to Search Across Multiple Columns and Return the Header

I have a nifty formula that allows you to search for a value in any number of columns and return the corresponding header. Here’s how to use it:

=Query(transpose(query({C2:E},"Select * where "&join(" or ",ArrayFormula("Col"&column(C2:E2)-column(C2)+1&" ='"&B2&"'")),1)),"Select Col1 where Col2='"&B2&"'",0)

This formula works by dynamically searching the specified columns for the search key and returning the header of the found column. It’s incredibly versatile and can handle any number of columns.

To adjust the formula for a different range of columns, simply change the references in the formula. For example, if you want to search columns C2:H, change the formula accordingly.

Now you can search across columns and effortlessly return the corresponding header in Google Sheets!

Formula Explanation (Search Multiple Columns)

Let’s dive deeper into how this dynamic formula works. Here’s a step-by-step breakdown:

1. The inner Query formula retrieves the row that contains the search key:

=query({C2:H},"Select * where "&join(" or ",ArrayFormula("Col"&column(C2:H2)-column(C2)+1&" ='"&B2&"'")),1)

This formula uses the Query function to search for the search key in each column and return the respective row.

2. The Transpose function wraps the inner Query formula, making the output vertical:

3. The outer Query formula treats the two-column data as a range and returns the header (column 1 value) if the column 2 value matches the search key:

=Query(transpose(query({C2:E},"Select * where "&join(" or ",ArrayFormula("Col"&column(C2:E2)-column(C2)+1&" ='"&B2&"'")),1)),"Select Col1 where Col2='"&B2&"'",0)

This dynamic formula combines the power of Query and Transpose to efficiently search across multiple columns and return the header in Google Sheets.

Lambda Solution

I also have another solution for you, called the Lambda solution. This solution works for several columns and even returns the header for multiple occurrences of a name. Here’s the formula:

=textjoin(",",true,BYCOL(C2:E11,LAMBDA(c,IFNA(IF(MATCH(B2,c,0),INDEX(c,1),""),""))))

This formula is also compatible with Excel. If you want to learn more about it, check out my related tutorial for searching a value and returning its header in Excel 365.

In conclusion, when it comes to searching across multiple columns and returning the header in Google Sheets, you have a few options. If your data contains 2-3 columns, you can utilize the Match formula. However, if you have a larger number of columns, the Query or Lambda solutions are the way to go.

Discover the power of Google Sheets with Crawlan.com, your go-to resource for all things Google Sheets. Happy searching!