What Do You Mean by Lookup and Retrieve the Column Header in Google Sheets?
I must answer this first before explaining the formula.
It means searching for a value in the first column of a table, evaluating that row for specific conditions, and then returning the header(s) of the matching conditions.
This is useful in scenarios like looking up an employee’s name in the first column and, if the row contains “Absent,” retrieving the corresponding headers with dates. This way, you can find on which dates an employee was absent.
Lookup and Conditionally Retrieve the Column Header (Single Criterion)
The sample data is in A2:F6. I want to look up the name Julia in A2:A6, match “A” in the found row, and return the corresponding headers.
Which is the better function in this case?
You may have a preconceived idea of using VLOOKUP, HLOOKUP, FILTER, or INDEX-MATCH. But for me, the QUERY function seems easier.
Formula:
=QUERY(TRANSPOSE(QUERY(A2:F6, "Select * where A='Julia'", 1)),"Select Col1 where Col2='A'", 0)This formula would return the dates 3/1/18 and 5/1/18 because the person in question (Julia) was absent on those days.
Note: The formula is case-sensitive. To make it case-insensitive, make the following changes:
- Replace
A='Julia'withLOWER(A)='julia'in the inner query. - Replace
Col2='A'withLOWER(Col2)='a'in the outer query.
The updated formula would look like this:
=QUERY(TRANSPOSE(QUERY(A2:F6, "Select * where LOWER(A)='julia'", 1)), "Select Col1 where LOWER(Col2)='a'", 0)How Does This QUERY Formula Work?
- The inner QUERY returns the header row and the row containing the name Julia in column A.
- So, we have two rows: the first with headers (dates) and the second with the status “P” and “A,” where “P” stands for Present and “A” for Absent.
- The TRANSPOSE function wraps the inner QUERY and converts the two-row output into two columns:
- The first column contains the headers.
- The second column contains the values “P” and “A.”
- The outer QUERY filters and returns the first column (headers) where the second column matches “A.”
This formula can be used to look up and retrieve column headers in Google Sheets. Its advantage is that it conditionally returns the column headers.
How Can I Adjust the Formula If My Data Range Is Different?
Take a look at the formula again:
=IFERROR(QUERY(TRANSPOSE(QUERY(A2:F, "Select * where A='Julia'", 1)),"Select Col1 where Col2='A'", 0))Changes:
- The range has been updated from A2:F6 to A2:F, making it open-ended.
- The formula is enclosed within the IFERROR function.
Benefits:
- The formula can support an unlimited number of rows.
- If the search key is not present in the first column, the result will be blank instead of showing an error.
If your number of columns differs, adjust the range reference from A2:F to something like A2:Z. No other changes are required in the formula.
Lookup and Conditionally Retrieve the Column Header (Multiple Criteria)
The following sample data shows the class-wise dress color codes on weekdays in a school.
Let’s create a formula to find the classes with the dress color code Blue on Wednesday.
- The search key is “Wednesday” in column A.
- Once found, search that row for the condition “Blue” and return the corresponding column headers, i.e., the class names.
Formula:
=IFERROR(QUERY(TRANSPOSE(QUERY(A1:K, "Select * where A='Wednesday'", 1)),"Select Col1 where Col2='Blue'", 0))This formula is similar to the earlier one. The data range, condition, and search key are different.
To Look Up Multiple Values:
If you want to look up “Wednesday” and conditionally return the column headers for the colors “Blue” or “Red,” use this formula:
=IFERROR(QUERY(TRANSPOSE(QUERY(A1:K, "Select * where A='Wednesday'", 1)),"Select Col1 where Col2='Blue' OR Col2='Red'", 0))If your criterion is a number, you can specify it directly without enclosing it in single quotes. You can learn more about this here: Examples of the Use of Literals in QUERY in Google Sheets.
Resources
- Search Across Columns and Return the Header in Google Sheets
- HLOOKUP to Search Entire Table and Find the Header in Google Sheets
- Find Max N Values in a Row and Return Headers in Google Sheets
- Column Header of Max Value in Google Sheets Using Array Formula
- How to Retrieve Column Header of Min Value 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
- Search Tables in Excel: Dynamic Filtering for Headers & Data
Hi,
Thank you for the great tutorial. I am trying to return not only the header of each column, but an additional value from another column. I have an example of what I need here.
One thing that is tripping me up is that I have the search key appearing multiple times. I have a sample of the data with a better description than what I have been able to describe here: [URL removed by Admin]
I would greatly appreciate it if you would take a look at this.
Thank you so much,
D.E.
Hi Darrell Ellis,
The problem you mentioned is not inline with the topic of this tutorial. However, I have coded two formulas that you can try.
Since you have not given me edit access to your sheet, you will need to add the formulas yourself. The formulas are as follows:
J2:
=LET(month,A2:A,bus,B2:B,test_range,C2:G,drop_down,I1,FILTER(FILTER(bus,month=drop_down),
BYROW(FILTER(test_range,month=drop_down),LAMBDA(r,COUNTIF(r,"X")))))
K2:
=MAP(TOCOL(J2:J,1),LAMBDA(r,LET(ftr,FILTER(C2:G,A2:A=I1,B2:B=r),
FILTER(C1:G1,ftr="X"))))
Thank you so much. I greatly appreciate your time and assistance
Hey Prashanth,
How can I get an output in one cell (possibly separated by
char(10)) rather than in multiple cells?Hi, Ninad,
Simply use the TEXTJOIN as below.
=textjoin(char(10),true,query_formula)Hi,
Is there any way to combine this with a Dropdown list so that in line with your first example, a cell contains a dropdown list containing the names in Column A, and depending on the name selected from the list, the Query formula would output the Column Headers (Attendance Dates) with “A”
This is basically to replace having to edit the formula every time to change the column A reference (name) as in my purpose, column A is frequently updated and changed.
Hi, Arthur,
Assume the drop-down is in cell G2. Then you can use the below Query for the range A2:F.
=iferror(query(transpose(query(A2:F,"Select * where A='"&G2&"'",1)),"Select Col1 where Col2='A'"))This tutorial may help you to learn the usage – Examples to the Use of Literals in Query in Google Sheets.
Hi Prashanth, is there a way to limit the query without a search key, and lock it to the current row? So if there is a cell(s) with TRUE in the row the formula is in, the query returns the header of the TRUE column?
=transpose(query(transpose(query($B$1:$E$5,"Select * where A="""&$B2&"""",1)),"Select Col1 where Col2=TRUE"))but instead of B2 as a search query, it would look at B2:E2 for TRUEs, and return the header from B1:E1? Then on the next row down, it would look at B3:E3 for TRUEs and return the header from row B1:E1.
Thanks
Hi, James,
To filter header row, based on TRUEs in current row use FILTER instead of QUERY.
Eg.
=filter($B$1:$E$1,B2:E2=TRUE)This will return values from B1:E1 if B2:E2 contains TRUE. It will expand the result horizontally based on number of TRUEs. So make sure that there are sufficient blank cells for this formula to expand.
You can also consider combining the output. In that case, you can try this one.
=textjoin(", ",true,filter($B$1:$E$1,B2:E2=TRUE))The above (either of the one) formula is for row # 2 (B2:E2). You can drag-down this formula for other rows.
Thank you for this – how can I output the result in a row rather than a column?
Insert the formula within TRANSPOSE(), like,
=transpose(formula_here)Thanks, may I know how can I use the search key with cell reference?
Hi, MPP,
In Query, it depends on the search key type (text/date/number). All detailed here in this guide;
Examples to the Use of Literals in Query in Google Sheets.