What do you mean by lookup and retrieve the column header in Google Sheets? I must answer this first before the formula.
I have a list/table in Google Sheets with multiple columns, in which I want to search down the very first column for a key.
If found, I want the column headers based on one condition.
This screenshot better depicts what I meant by lookup and retrieving the column header in Google Sheets.
I want to search down the name “Julia” in column A and, if found, return the header row which contains the dates.
But it should be a conditional return. I want to get the date(s) if the person was absent (A).
Which is the better function in this case?
You may have a preconceived idea of using Vlookup, Hlookup, or Index-Match.
But for me, the Query seems easier.
Formula:
=query(transpose(query(A2:F6,"Select * where A='Julia'",1)),"Select Col1 where Col2='A'")This formula would return the dates 3/1/18 and 5/1/18 because the person in question (Julia) was absent on those days.
How Does This Query Formula Work?
The inner Query returns the header row and the row that contains the name Julia in Col1.
So we have two rows – the first row with headers (dates) and the second row with the status “P” and “A” where “P” stands for Present and “A” stands for Absent.
The Transpose function that wraps the inner Query makes these two-row outputs into two columns.
The first column contains the headers, and the second has the values “P” and “A.”
The outer Query returns Col1 (column 1) if Col2 (column 2) matches “A.”
You can use this formula to look up and retrieve the column header in Google Sheets.
The plus point of this formula is that it conditionally returns the column header.
How Can I Adjust the Formula If My Data Range Is Different?
Once again, see the above formula.
This time in the below formula, I have made the range infinite.
=iferror(query(transpose(query(A2:F,"Select * where A='Julia'",1)),"Select Col1 where Col2='A'"))Earlier, it was A2:F6. Additionally, I have enclosed the formula within the IFERROR function.
These modifications can help you in two ways.
- First, the formula can support an unlimited number of rows.
- Secondly, if the search key is not present in the first column, the result will be blank instead of an error.
If your number of columns is different, change the reference A2:F to A2:Z or something like that.
There are no other changes required in the formula.
Can You Provide Us With One More Example?
Why not? Here you go!
Example 2: Lookup and Conditionally Retrieve the Column Header
The below sample data shows the class-wise dress color codes on weekdays in a school.
Let’s code a formula to find the classes with the dress color code Blue on Wednesday.
This time the search key is “Wednesday” in column A.
Once found, search that row for matching conditions, i.e., uniform color Blue and return the corresponding column header, i.e., the class name.
Formula:
=iferror(query(transpose(query(A1:K,"Select * where A='Wednesday'",1)),"Select Col1 where Col2='Blue'"))The formula is similar to our first example formula. The data range, condition, and search key are different.
I hope you find the above example handy in your real life.
Thanks for the stay!
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.