Google Sheets QUERY function: a spreadsheet cure-all you have yet to discover

If you've been following this blog for a while, you may remember QUERY function for Google Sheets. I mentioned it as a possible solution for a couple of cases. But those are far from enough to uncover its full potential. Today, it's high time we get to know this spreadsheets superhero properly. And guess what – one equally noteworthy tool will also be there :)

Did you know that Google Sheets QUERY function is considered to be the most powerful one in spreadsheets? Its peculiar syntax favours tens of different operations. Let's try and break its parts down to learn them once and for all, shall we?

Syntax of Google Sheets QUERY function

At first glance, Google Sheets QUERY is just another function with 1 optional and 2 required arguments:

=QUERY(data, query, [headers])
  • data is the range to process. Required. Everything is crystal clear here.

    Note. Only one small reminder here established by Google: each column should contain one type of data: textual, or numeric, or boolean. If there are different types, QUERY will work with the one that occurs the most. Other types will be considered as empty cells. Strange, but keep that in mind.

  • query is the way to process the data. Required. This is where all the fun begins. Google Sheets QUERY function uses a special language for this argument: Google Visualization API Query Language. It's written in a way similar to SQL. Basically, it's a set of special clauses (commands) used to tell the function what to do: select, group by, limit, etc.

    Note. The entire argument must be enclosed in double-quotes. Values, in their turn, should be wrapped in quotation marks.

  • headers is optional for when you need to indicate the number of header rows in your data. Omit the argument (as I do below), and Google Sheets QUERY will assume it based on the contents of your table.

Now let's dig deeper into the clauses and whatever they do.

Clauses used in Google Sheets QUERY formulas

Query language consists of 10 clauses. They may frighten at first glance, especially if you're not familiar with SQL. But I promise, once you get to know them, you will get a powerful spreadsheet weapon at your disposal.

I'm going to cover each clause and provide formula examples using this list of imaginary students and their paper subjects: Different types of data for the QUERY function.

Yep, I'm one of those weirdos who think Pluto should be a planet :)

Tip. Several clauses can be used within one Google Sheets QUERY function. If you nest them all, make sure to follow the order of their appearance in this article.

Select (all or specific columns)

The very first clause – select – is used to tell what columns you need to return with Google Sheets QUERY from another sheet or table.

Example 1. Select all columns

To fetch each and every column, use select with an asterisk – select *

=QUERY(Papers!A1:G11,"select *") Select all columns.

Tip. If you omit the select parameter, Google Sheets QUERY will return all columns by default:

=QUERY(Papers!A1:G11)

Example 2. Select specific columns

To pull only certain columns, list them after the select clause:

=QUERY(Papers!A1:G11, "select A,B,C") Return specific columns only.

Tip. The columns of interest will be copied in the same order you mention them in the formula:

=QUERY(Papers!A1:G11, "select C,B,A") Return certain columns in another order.

Google Sheets QUERY – Where clause

Google Sheets QUERY where is used to set the conditions towards the data you want to get. In other words, it acts as a filter.

If you use this clause, QUERY function for Google Sheets will search columns for values that meet your conditions and fetch all matches back to you.

Tip. Where can function without the select clause.

As usual, to specify conditions, there are sets of special operators for you:

  • simple comparison operators (for numeric values): =, <>, >, >=, <, <=
  • complex comparison operators (for strings): contains, starts with, ends with, matches, != (doesn't match / doesn't equal to), like.
  • logical operators to combine several conditions: and, or, not.
  • operators for blank / not empty: is null, is not null.

Tip. If you're upset or worried about having to deal with such a huge number of operators again, we feel you. Our Filter and Extract Data will find all matches and build QUERY formulas in Google Sheets for you if necessary.

Let's see how these operators behave in formulas.

Example 1. Where with numbers

I will add where to my Google Sheets QUERY from above to get the info on those planets that have more than 10 moons:

=QUERY(Papers!A1:G11,"select A,B,C,F where F>=10") Select columns based on the condition for numbers.

Tip. I also mentioned column F to fetch just to make sure the criterion is met. But it's completely optional. You don't have to include columns with conditions into the result:

=QUERY(Papers!A1:G11,"select A,B,C where F>=10")

Example 2. Where with text strings

  • I want to see all rows where the grade is either F or F+. I will use the contains operator for that:

    =QUERY(Papers!A1:G11,"select A,B,C,G where G contains 'F'") Select columns based on text.

    Note. Don't forget to surround your text with quotation marks.

  • To get all rows with F only, just replace contains with an equal sign (=):

    =QUERY(Papers!A1:G11,"select A,B,C,G where G='F'")

  • To check the papers that are yet to be delivered (where the grade is missing), check column G for blanks:

    =QUERY(Papers!A1:G11,"select A,B,C,G where G is null'")

Example 3. Where with dates

Guess what: Google Sheets QUERY has even managed to tame dates!

Since spreadsheets store dates as serial numbers, usually, you have to resort to the help of special functions like DATE or DATEVALUE, YEAR, MONTH, TIME, etc.

But QUERY has found its way around dates. To enter them properly, simply type the word date and then add the date itself formatted as yyyy-mm-dd: date '2022-01-01'

Here's my formula to get all rows with a Speech date before 1 Jan 2022:

=QUERY(Papers!A1:G11,"select A,B,C where B<date '2022-01-01'") Select columns based on a certain date.

Example 4. Combine several conditions

To use a certain period of time as a criterion, you will need to combine two conditions.

Let's try and retrieve those papers that were delivered in Autumn, 2019. The first criteria should be a date on or after 1 September 2021, the second — on or before 30 November 2021:

=QUERY(Papers!A1:G11,"select A,B,C where B>=date '2021-09-01' and B<=date '2021-11-30'") Combine several conditions with AND logic.

Or, I can select papers based on these parameters:

  • before 31 December 2021 (B<date '2021-12-31')
  • have either A or A+ as a grade (G contains 'A')
  • or B/B+ (G contains 'B')

=QUERY(Papers!A1:G11,"select A,B,C,G where B<date '2021-12-31' and G contains 'A' or G contains 'B'") Use AND and OR logic operators in the Google Sheets QUERY When clause.

Tip. If your head is about to explode already, don't give up just yet. There's a tool that is perfectly capable of building all these formulas for you, no matter the number of criteria. Jump right to the end of the article to get to know it.

Google Sheets QUERY – Group By

Google Sheets QUERY group by command is used to concatenate rows. However, you should use some aggregate functions in order to summarize them.

Note. Group by must always follow the select clause.

Unfortunately, there's nothing to group in my table as there are no recurring values. So let me adjust it a bit.

Suppose, all the papers are to be prepared by 3 students only. I can find the highest grade each student got. But since they are letters, it is the MIN function I should apply to column G:

=QUERY(Papers!A1:G11,"select A,min(G) group by A")

Note. If you don't use an aggregate function with any column in the select clause (column A in my example), you must duplicate them all in the group by clause.

Group rows using QUERY function in Google Sheets.

Google Sheets QUERY – Pivot

Google Sheets QUERY pivot clause works the other way around, if I may say so. It transposes data from one column to a row with new columns, grouping other values accordingly.

For those of you dealing with dates, it can be a real discovery. You'll be able to get a quick glance at all the distinct years from that source column.

Note. When it comes to pivot, every column used in the select clause should be covered with an aggregate function. Else, it should be mentioned in the group by command following your pivot.

Remember, my table now mentions only 3 students. I'm going to make the function tell me how many reports each student made:

=QUERY(Papers!A1:G11,"select count(G) pivot A") Use the pivot clause to produce another table.

Google Sheets QUERY – Order By

This one is pretty easy :) It is used to sort the outcome by the values in certain columns.

Tip. All previous clauses are optional when using order by. I use select to return fewer columns for demonstration purposes.

Let's go back to my original table and sort reports by speech date.

This next Google Sheets QUERY formula will get me columns A, B and C, but at the same time will sort them by date in column B:

=QUERY(Papers!A1:G11,"select A,B,C order by B") Get the data sorted by a certain column.

Tip. You can learn everything about sorting data (by date, colors, etc.) in Google Sheets in this article.

Limit

What if I told you, you don't have to bring each and every row into the result? What if I told you that Google Sheets QUERY can pull only a certain amount of the first matches it finds?

Well, the limit clause is designed to help you with that. It limits the number of rows to return by the given number.

Tip. Feel free to use limit without other previous clauses.

This formula will show the first 5 rows where the column with grades contains a mark (is not empty):

=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null limit 5") Pull a specific number of rows from the beginning.

Offset

This clause is kind of opposite to the previous one. While limit gets you the number of rows you specify, offset skips them, retrieving the rest.

Tip. Offset also doesn't require any other clauses.

=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null offset 5") Skip rows at the beginning using offset.

If you try and use both limit and offset, the following will happen:

  1. Offset will skip rows at the beginning.
  2. Limit will return a number of the following rows.

=QUERY(Papers!A1:G11,"select A,B,C,G where G is not null limit 3 offset 3")

Out of 11 rows of data (the first one is a header and QUERY function in Google Sheets does a nice job understanding that), offset skips the first 3 rows. Limit returns 3 next rows (starting from the 4th one): Use both limit and offset in Google Sheets QUERY.

Google Sheets QUERY – Label

Google Sheets QUERY label command lets you change header names of the columns.

Tip. Other clauses are optional for label as well.

Put the label first, followed by the column ID and a new name. If you rename few columns, separate each new pair of column-label by a comma:

=QUERY(Papers!A1:G11,"select A,B,C label A 'Name', B 'Date'") Rename columns using Google Sheets QUERY label clause.

Format

The format clause makes it possible to alter the format of all values in a column. For that, you will need a pattern standing behind the desired format.

Tip. The format clause can also play solo in the Google Sheets QUERY.

=QUERY(Papers!A1:G11,"select A,B,C limit 3 format B 'mm-dd, yyyy, ddd'") Format values right through QUERY function in Google Sheets.

Tip. I mentioned some date formats for Google Sheets QUERY in this blog post. Other formats can be taken directly from spreadsheets: Format > Number > More Formats > Custom number format.

Options

This one is used to set some additional settings for the outcome data.

For example, such command as no_values will return formatted cells only.

The quickest way to build QUERY formulas – Filter and Extract Data

However powerful the QUERY function in Google Sheets is, it may require a learning curve to get ahold of. It's one thing to illustrate each clause separately on a small table, and completely another to try and build everything correctly with a few clauses and a much bigger table.

That's why we decided to dress Google Sheets QUERY up in a user-friendly interface and make it the add-on.

Why Filter and Extract Data is better than formulas?

Well, with the add-on there's absolutely no need to:

  • figure out anything about those clauses. It's really easy to create lots of complex conditions in the add-on: as many as you need despite their order to fetch as many matches as you need.

    Note. At the moment, the following clauses were incorporated into the tool: select, where, limit, and offset. If your task requires other clauses as well, please comment below – perhaps, you'll help us improve ;)

  • know how to enter operators: just pick the one from a drop-down list.
  • puzzle over the correct way to enter date and time. The add-on lets you enter them as you used to based on your spreadsheet locale.

    Tip. There's always a hint available in the tool with examples of different data types.

As a bonus, you'll be able to:

  • preview both the result and the formula
  • make quick adjustments to your criteria
  • select a place for the result
  • insert the result as both QUERY formula or as values

I'm not kidding, see for yourself:

I hope you will give the add-on a chance and get it from Google Workspace Marketplace. Don't be shy and share your feedback, especially if there's something about it you don't like.

Also, feel free to check out its tutorial page or home page.

Table of contents

157 comments

  1. Hi Natalia,
    I have sheet1 and sheet2. On sheet1 there are two types of data below each other. type1 is manually data and type2 is query+importrange. On sheet2 I wanted to make a query based on sheet1 but not works with the query data. If I write manually in sheet1 its works on sheet2.

    So in sheet1:

    Col1 | Col2
    KBL-001|EU
    KBS-002|Magyar
    LEO-001|Magyar
    SEO-002|EU **//this is a query+importrange\\**

    sheet2
    =QUERY({'sheet1'!A2:B};"select Col1, Col2 where not Col1 contains 'seo' or not Col1 contains 'leo'")

    Its works on LEO-001 but SEO-002 not. I tried importrange insted of 'sheet1'!A2:B too but does not work. In my opinion the problem is with the query based query. Is there any solution?

    Thanks in advance

    • Hi Zsolt Gál,

      I'm really sorry, I overlooked your comment. Do you still need a solution? If so, please consider sharing your spreadsheet along with your formula with us (support@apps4gs.com). If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into the problem and try to find a solution.

  2. Hi, can you please help me to understand how to do better in this case, I want to add different condititions for my imported data, but once I delete the value from one of sell, the origin files import all field to the current file.

    =query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")},
    "select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col18
    where (Col8 contains '"&B2&"')
    or (Col8 contains '"&C2&"')
    or (Col8 contains '"&D2&"')
    and (Col8 is not null)
    and (Col5 is not null)
    order by Col6 asc,
    Col10 asc",0)

    https://docs.google.com/spreadsheets/d/1gYMouKgMmcD4g27dZnR5M2pQwxMEHOhM8xJunr7CHKg/edit?usp=sharing

    • Hi Ann,

      Thank you for sharing the file. Unfortunately, it's hard to tell what's happening because I have no editing access to your spreadsheet. I tried to reproduce the problem on dummy data on my side but everything works correctly there. Perhaps, you could share an editable copy of your file with me? You can share it directly with support@apps4gs.com

      Also, please specify if you delete the value from the origin file or from this spreadsheet. Which value do you remove exactly? What happens with the data returned by QUERY once you remove that value? You can also add more sheets to your file featuring your data before/after removing the value.

      Thanks!

      • I just shared the file with you.

        The issue is
        if I delete - Select a state 3 MN (CELL D2) - then there is a mess with the imported data.

        I just want to give a condition, if one of my B2, C2 or D2 is empty
        import only the rows from my query which contains one of them

        In my case, if D2 is empty the formula is importing all empty spaces from my main spreadsheet

        • Thank you for editing permissions and additional example sheets!

          I duplicated your sheets without formulas and entered the correct QUERY there. It's the same formula on both sheets, looks like what you need :)

          =QUERY({IMPORTRANGE(Abb!F3,"MC2!A11:S5000")},
          "select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col18
          where (Col8 contains '"&IF(B2 = "",false, B2)&"')
          or (Col8 contains '"&IF(C2 = "",false, C2)&"')
          or (Col8 contains '"&IF(D2 = "",false, D2)&"')
          and (Col8 is not null)
          and (Col5 is not null)
          order by Col6 asc,
          Col10 asc",0)

  3. Hi Natalia,

    Thank you for sharing this, I am building a dashboard using query, I would like to know if there is a way to insert a sumproduct function within query in google sheet, if so, please let me know how, that is one step that I need to complete my dashboard, appreciate it!

    All the best!

    • Hi Gail,

      For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data with the QUERY (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your data and try to find a solution.

      • Hi Natalia,

        Apologies for replying late, I was able to solve the problem; thank you so much for the support

  4. I need Two Condition (1) IF I do not select anything from the Drop Down still My Entire Data gets reflected (2) and if I select A particular name from the Drop Down List the the Data Related to that Client gets extracted.
    Please assist.

    • Hello Sumit,

      please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) your source data, (2) the result you want to get.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      If you have confidential information there, you can replace it with some irrelevant data, just keep the format. I'll look into it and try to help.

  5. Hello!

    I want to generate a quotation from múltiple variables from a list (Gender, Age, Product, etc). In order to do it I need a specific rate from another table and I am using the Query function as you shown but need to include a Vlookup function into the query, is it posible?

    This is where I am.
    =QUERY(Rates!A3:AN215,"SELECT * WHERE ((lower(A) = lower('"&Cotizador!D14&"'))) AND ((lower(B) = lower('"&Cotizador!D8&"'))) AND ((lower(C) = lower('"&Cotizador!D12&"'))) AND ((D = "&Cotizador!D10&"))", true)

    After bringing the results given those criterias, I need then it to go and pick the rate from a specific column where the header is the age so after I get that value, calculate the formula for the quotation.

    Thanks!

    • Hello Marino,

      I'm sorry but your task is not really clear. For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your data and try to find a solution.

  6. Hello,
    I am trying to write a query that only returns the columns where the matching row value is greater than 0.
    Here is the part that works:
    query('Sub Cap Mat Requirements'!$A$1:$P$422,"Select F, G, H, I, J, K, L, M WHERE A = '"&$C$3&"' AND

    please could you help me finish it!

    In one example I don't want column M returned as the row value is 0, but if I change the value in C3, then it may be column J that's 0 so that would be the column I would want omitted from the results.

    Thanks in advance!

    • Hello Kahl,

      I'm afraid your formula won't work. Here's another one for you to try:
      =QUERY({A1:P422},"select "&ArrayFormula(JOIN(",","Col"&QUERY({TRANSPOSE({QUERY({A1:P422},"select * where Col1 = '"&C3&"' limit 1",1);ARRAYFORMULA(COLUMN(A1:P1)-1)})},"Select Col3 where Col2 <> 0",1)+1)),1)

  7. Thank you!???

    Does anyone know how to =Query from multiple sheets?

  8. What a gold mine, thank you!!

    Is it possible to search by column and row? I'm having trouble formatting it.
    For instance: "SELECT * WHERE Col1='Revenue' AND Row1='2022' "

    • Thank you for your feedback, Ohad!

      I'm sorry I don't really understand what this condition should return. Please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into it.

  9. Is there a way to limit the number of columns in the query pivot? I suppose the follow up to that would also be ordering it by the total of the pivoted columns

  10. How to use this query formula in excel to pull multiple column data from one tab to another

  11. Hi,

    I would like to return values from a different google sheet with details below:

    =query(IMPORTRANGE(B7,"UCaaS!B:B"),"Select * where B contains 'Citrix'")

    I am getting this error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: B

    Appreciate your help on this. Thank you.

  12. While getting data from different sheets in a main sheet through query function, it shows error"In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." when takes range A9:O and when I takes A8:O its working but it is taking headwer of all and I want only data.
    Query is :

    ={query(Ajmer!A9:O,"Select A,B,C,G,O where O='"&B1&"'");query(Sikar!A9:O,"Select A,B,C,G,O where O='"&B1&"'");query(Jhotwara!A9:O,"Select A,B,C,G,O where O='"&B1&"'");query(Chaksu!A9:O,"Select A,B,C,G,O where O='"&B1&"'");query(Bassi!A9:O,"Select A,B,C,G,O where O='"&B1&"'");query(Bijaynagar!A9:O,"Select A,B,C,G,O where O='"&B1&"'");query(Bhilwara!A9:O,"Select A,B,C,G,O where O='"&B1&"'");query(Chomu!A9:O,"Select A,B,C,G,O where O='"&B1&"'");query(Chittorgarh!A9:O,"Select A,B,C,G,O where O='"&B1&"'")}

    • Hello Chandra,

      This error usually occurs whenever there is a blank sheet or a sheet with only a header row among your original tables. Can you check that?

  13. Hi,
    We are using the query function to get data from a Master Sheet(let's say SheetA) and show it another Sheet (SheetB). We are facing issues due to the high number of people (~1000) accessing the sheets. Is there a way to identify the number of active connections made by query function so that we can alert the users in some way? Please suggest if there is a better workaround to navigate this number of users problem?
    Thanks

  14. Can I overwrite data that has been queried?
    I have my query set to pull based on date, I then need to overwrite the cell that contains "yes". Can this be set up without deleting all my data?

  15. Is there a limit to the select range of rows or cells it will work with?
    For example if I used this QUERY(Calculations!A2:T1500, "select A, B, C where (S>0)", 1)
    It'll return one line of data.
    If the I used this QUERY(Calculations!A2:T1200, "select A, B, C where (S>0)", 1) Note its 1200 rows not 1500 it will return all the lines of data.
    Thanks

    • Hello Max,

      The formula always works within the range you indicate. If you limit it by 1200 rows, it won't look at rows 1201 and further.
      In case you need an unlimited number of rows (include all future rows in the result), you need to use a reference like this: Calculations!A2:T

  16. Hello,

    I have a query function pulling specific lines from one tab to separate tabs based on information provided in specific columns (info coming in from a connected google form). The query was working well and then suddenly started filtering all of the information into a single line in each tab. Examples of the queries used are:

    =query('Form Responses 1'!A2:BJ, "Select*where C='Leadership / Dr. J' and L='Yes'")
    =query('Form Responses 1'!A2:BJ, "Select*where C='Green Energy - Dr. Smith' and L='Yes'")

    The problem persists even when I try to simplify the query:
    =query('Form Responses 1'!A2:BJ, "Select*where C='Leadership / Dr. J'")

    Any support in figuring out why the queries stopped sorting and how to get them up and running again would be very much appreciated.

    Thanks!

    • Hello Marie,

      For me to be able to help you, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com). If you have confidential information there, you can replace it with some irrelevant data, just keep the format. I'll look into it.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  17. Hi!
    I hope you're doing well.

    I want to use the query function to display a data set where the query looks like
    =QUERY('Student Data Dump'!A2:K, "SELECT * where F='International Business Management - Logistics System (B999)'")

    However rather than typing the F column manually, I want to assign it to a drop down cell. So that I could select what data to display based on what the A2 cell has.
    I tried the same with =QUERY('Student Data Dump'!A2:K, "SELECT * where F= IBM-LS!A2 ") but that doesn't work.
    Could you help me out with the same? Thanks!
    The link to the sheet is https://docs.google.com/spreadsheets/d/1NIPzSZ1nCNTgekDN5-40NLAPxKg6lszFpVJFMFIFHVM/edit#gid=192360295

    • Hi Nitish,

      Your sample spreadsheet is locked from editing. The correct way to reference other cells in QUERY formulas is like this:
      =QUERY('Student Data Dump'!A2:K, "select * where F = '"&'IBM-LS'!A2&"'")

  18. Hello, I have a query where I have the top 10, but am trying to find the next 10 now
    =query(A:BA), "SELECT Col10, sum(Col51)/1000 WHERE Col9='XXX' Group by Col10 Order by sum(Col51)/1000 desc limit 10 LABEL Col10 '', sum(Col51)/1000 ' '",-1)
    Offset only removes the first 10 rows from the original data set, not from the grouping, so was hoping to group desc next 10 if that's possible?
    Thanks!

    • Hello Lola,

      Offset is exactly the clause that you'd use to find 'the next 10'. Can you please share an editable copy of your file with us (support@apps4gs.com) where you used the formula and it didn't work? Also, please include the example of the result you'd like to get, it will give us the exact picture of your task. Thank you.

  19. Hello,
    I have 15 columns with Group tag as header and containing lists of names.
    Some names appears in different columns / Group tags.
    How can I display in a single cell the different columns headers / Group tags where a specific name is appearing?
    Thank you in advance,

    • Hello Pierre,

      For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) an example of your source data (if you have confidential information there, you can replace it with some irrelevant data, just keep the format) (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into it.

      • Hello Natalia,
        The file has been shared with you.
        Thank you in advance once again,
        Pierre

        • Hello Pierre,

          Please look at column N in your spreadsheet. I placed the formula to N2 and copied it down:
          =TEXTJOIN(", ",TRUE,QUERY({QUERY({TRANSPOSE(QUERY($B$1:$J$10,"select *",1))},"select Col"&TEXTJOIN(",Col",TRUE,ArrayFormula(ROW(INDIRECT("1:"&ROWS({TRANSPOSE(QUERY($B$1:$J$10,"select *",1))})))))&" where Col"&TEXTJOIN("='"&M2&"' or Col",TRUE,ArrayFormula(ROW(INDIRECT("1:"&ROWS({TRANSPOSE(QUERY($B$1:$J$10,"select *",1))})))))&"='"&M2&"'",0)},"select Col1",0))

  20. I am using Query in a google sheet , but all the results are coming together in the 1st row only and from the 2nd row it is coming properly .

    For example : Data should be like..
    Col 1 Col 2 Col 3
    A B. C
    D. E. F

    But the Data is coming like after using Query function :

    Col 1. Col 2. Col 3
    A D. B E C F
    D. E. F

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)