In Google Sheets, you might often need to search for a specific value across multiple columns and return the corresponding column header. This is particularly useful when you’re working with structured data, where categories are listed in headers and associated data is placed below them. For example, you may have multiple projects listed in the headers and machinery deployed under each project. In this case, you might want to search for a specific machinery type and return the project it is allocated to.
In this tutorial, you’ll learn three different formulas for searching across columns and returning matching headers. Each formula serves a different purpose:
- A case-insensitive match formula.
- A modified version of the first formula that allows partial matches.
- A case-sensitive match formula for exact keyword matching.
Below is a screenshot to show how the search across columns and return header works with Formula Approach 1.

Case-Insensitive Formula to Search Across Columns and Return the Header in Google Sheets
Consider the following data range: A1:C
, where A1:C1
contains the project names (e.g., Highway Expansion, Skyscraper Build, Bridge Construction).
Let’s say you want to search for “Concrete Mixers” under these projects and return the project name(s). If the same machinery is deployed under multiple projects, you’ll want to return all matching project names.
Exact Match:
- Enter the keyword “Concrete Mixers” in cell
E2
. - Enter the following formula in cell
E3
:
=TOROW(BYCOL(A1:C, LAMBDA(col, IF(XMATCH(E2, col), INDEX(col, 1)))), 3)
This formula is case-insensitive and searches across the columns to return the header in Google Sheets.

How Does This Formula Work?
XMATCH(E2, col)
checks for a match of the search key in the current column and returns the relative position if a match is found; otherwise, it returns#N/A
.IF(.., INDEX(col, 1))
checks if the XMATCH function returns a number. If so, the INDEX function returns the first row of the current column (i.e., the header).- BYCOL applies this logic to each column in the array, returning either the header or
#N/A
values. - TOROW removes any error values, leaving only the matched headers.
Partial Match:
To allow for partial matching, you can modify the formula by using the wildcard *
on both sides of the search key in XMATCH, and specifying match mode 2
.
The updated formula will be:
=TOROW(BYCOL(A1:C, LAMBDA(col, IF(XMATCH("*"&E2&"*", col, 2), INDEX(col, 1)))), 3)
This will allow partial matches of the search key, and it’s still case-insensitive.
Simply enter “Mixers” in cell E2. The formula will locate “Concrete Mixers” in the columns and return the corresponding header.
Case-Sensitive Formula to Search Across Columns and Return the Header in Google Sheets
In some cases, case sensitivity is important, especially when you’re dealing with item codes, usernames, etc.
Using the same data set from above, here’s the formula to search for an exact case-sensitive match and return the headers:
=TOROW(BYCOL(A1:C, LAMBDA(col, INDEX(col, IF(SORTN(EXACT(col, E2), 1, 0, 1, 0), 1, -1)))), 3)
Formula Explanation:
EXACT(col, E2)
compares the search key with each value in the current column and returns an array ofTRUE
orFALSE
values.SORTN(..., 1, 0, 1, 0)
sorts the array in descending order, keeping theTRUE
values at the top (if a match is found).IF(..., 1, -1)
returns1
ifTRUE
(indicating a match) and-1
ifFALSE
.INDEX(col, ...)
returns the header of the current column if there’s a match, otherwise it returns#NUM
error.BYCOL
applies the formula to each column in the array and returns either the header or#NUM
error.TOROW
removes any errors, leaving only the valid headers.
This formula performs a case-sensitive search across columns and returns the corresponding headers.
Conclusion
By using these formulas, you can easily search across columns and return the header in Google Sheets. Whether you need an exact match, partial match, or case-sensitive match, these formulas provide flexible options for working with structured data in Sheets.
These techniques are especially useful when handling large datasets where manually searching for specific data could be time-consuming.
Related Resources:
- Lookup and Retrieve Column Headers in Google Sheets
- Find the Column Header of the Max Value in Google Sheets
- Find the Column Header of the Min Value in Google Sheets
- Find Max N Values in a Row and Return Headers in Google Sheets
- Get the Headers of the First Non-Blank Cell in Each Row in Google Sheets
- Get the Header of the Last Non-Blank Cell in a Row in Google Sheets
Thank you very much for this solution! Yes, it works, even with absolute references in the part [column($C2:$H2)-column($C$2)], if you want to drag e.g.
='"&B2&"'")
to B10. Very helpful.Hello Prashanth,
I have the following test Google Sheet. My goal is to take the data from A1:O10 and list it as laid out in cells A14:F29, with the query formula being in cell A15. Is this even possible? The sheet link is provided below.
[URL]
Thanks,
Jack
Hi Jack,
You need to unstack the data. Please review the formulas entered in your sheet.
I hope that helps.
Thanks Prashanth. It is very helpful. I will go read up on unstacking data.
This is very helpful – thank you! Is it possible to make the
'"&B2&"'
in the inner query a range? Or dynamic? Such as'"&B2:B&"'
Hi, Jason,
Nope! But there are better options now in Google Sheets to achieve your desired goal.
Please try this.
=map(B2:B10,lambda(r,textjoin(", ",true,filter(
index(split(flatten(C3:E&"|"&C2:E2),"|"),0,2),
index(split(flatten(C3:E&"|"&C2:E2),"|"),0,1)=r))))
Thank you very much!