Search Across Columns and Return the Header in Google Sheets

Published on

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:

  1. A case-insensitive match formula.
  2. A modified version of the first formula that allows partial matches.
  3. 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.

Search across columns and return the matching column header in Google Sheets

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:

  1. Enter the keyword “Concrete Mixers” in cell E2.
  2. 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.

Find the column header for a value in a table 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 of TRUE or FALSE values.
  • SORTN(..., 1, 0, 1, 0) sorts the array in descending order, keeping the TRUE values at the top (if a match is found).
  • IF(..., 1, -1) returns 1 if TRUE (indicating a match) and -1 if FALSE.
  • 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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

7 COMMENTS

  1. 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.

  2. 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, 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))))

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.