Comments on: 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. 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 :) Continue reading →

Comments page 2. Total comments: 158

  1. I am doing a QUERY(IMPORTRANGE) from one sheet into another. I want the function to select rows from the source sheet where the values in the first and second imported rows equal values in two cell of the importing sheet. The value in the first cell, to match Col1, is a number.

    The command =QUERY(IMPORTRANGE("18L1......","'RPCC Central Committee members'!C2:H12"),"select Col1,Col3,Col4,Col5,Col6 where Col1="&A4 &" "), it works fine to select rows where Col1 matches the value.

    The value in the second cell, to match Col2, is a letter.
    The command =QUERY(IMPORTRANGE("18L1.............","'RPCC Central Committee members'!C2:H12"),"select Col1,Col3,Col4,Col5,Col6 where Co21="&B4 &" ") produces the error: Error
    Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: C

    What am I doing wrong?

    1. Hello Conrad,

      I can see 'where Co21' in the second formula. This doesn't look correct and should be Col2 if I understand the task correctly. Please fix this first and see if the error disappears.

  2. Hello! Thank you for this great resource. I'm wondering if it's possible to to have the function SELECT particular columns based on the text in the column header, rather than the column number. So that maybe if a column in the source spreadsheet is shifted (but retains the same column header), the Query function would still get the same data.

    Thanks!

    1. Hello Nick,

      I'm afraid QUERY doesn't take column headers as identifiers. It works only with column IDs as in the examples in the article.

  3. Hello

    is it possible to get output if one of C5 or C6 is empty?

    =QUERY(Sheet1!A1:G30, "select * where D='"&C5&"' and C='"&C6&"' ")

    Any help is greatly appreciated. Thank you!

  4. Hallo Natalia!

    I'm trying to find get the correspondent value of said row, in column F, of the highest value in column E
    currently i got this =query('Raw Data'!A2:G,"Select D,max(E),min(F),max(F) where D is not null group by D,E")
    But i just want to keep the min value of F, and the correspondent value, of the max of E.
    Is there any way to do this?

    1. correction*
      to keep the min value of F, and the correspondent value in F, of the max of E.

      1. Hello Raul,

        For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with your data and the formula. Please also include the example of the result you expect to get. 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 it and try to help.

        1. Thanks for the reply

          File shared!

          1. Thank you for sharing the file, Raul.

            I'm a bit confused however because your text description doesn't match the actual table and your actual formula. I'm not sure about the criteria you use to get the data.

            Yet, from what I understand, I can suggest using QUERY to get the data based on columns A-D first, then enclose this into another QUERY as a range while adding other conditions for columns E-F. It'll look like =QUERY(QUERY(..., select ...), select ...)

            1. Yes, i realized so mistakes were done with the file i had prior to my first comment, so i had it updated.

              However i didn't know it was possible to place a query inside a query.
              Thank you very much for the tip!

              Best regards

  5. - My main data tab has about 10 new rows added each day.
    - My query tab has a query that pulls in certain things from each row, but still maintains each row and still adding about 10 new rows each day.
    - To the right of my query, I have several columns that contain COUNTIF formulas, counting each time certain things happen in each row.
    - The results from my COUNTIF formulas will feed into my Google Data Studio dashboard, and if I pull the formulas to the end of the page, zeros appear, which become a problem.

    My question: Is it possible to have the COUNTIF formulas add new rows when the query adds new rows? Is there a way to turn my query into a table so the formulas fill in automatically, such as with excel tables? Or is there an alternate formula that I can pull to the bottom of the page and not get zeros?

    1. Hi Amber,

      There are a few things you can do:

      • Incorporate the IF function so it looks whether cells are empty and if so, returns an empty string rather than COUNTIF calculations.
      • Or switch to COUNTIFS instead to make it calculate only non-empty cells right away.
      • Include ARRAYFORMULA to get the results for the whole column at once.

  6. Thank you Natalia. I've been looking for two days for how to order my query data, and this article solved it!

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

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

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

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

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

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

  9. 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!

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

      1. Hi Natalia,

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

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

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

  11. 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!

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

  12. 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!

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

  13. Thank you!???

    Does anyone know how to =Query from multiple sheets?

  14. 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' "

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

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

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

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

  18. 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&"'")}

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

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

  20. 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?

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

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

  22. 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!

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

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

    1. 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&"'")

  24. 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!

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

  25. 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,

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

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

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

          1. Thank you !

        2. Hello Pierre,

          Thank you, I've got the file. I'll look into it as soon as possible and try to come up with a solution.

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

  27. How can I add an order function to this query where it will sort both sets of data together in one list? Basically I want to pull two sets of data then have both sets ordered together in one long list.

    ={query('Reg Data'!A:AA,"Select F, E, H, K, U, R, V, X, G, Y, AA WHERE S='US History A'",0); query('Reg Data'!A:AA,"Select F, E, H, K, U, R, V, X, G, Z, AA WHERE T='US History A'",0)}

    I want to sort the entire list using this: Order by R, F, E

    Thank you for your help.

    1. Hello Neal,

      By adding the 'order by' clause to each QUERY, you will sort each range individually.

      To sort the entire result, you need to wrap QUERY in the SORT function:
      =SORT({QUERY('Reg Data'!A:AA,"select F,E,H,K,U,R,V,X,G,Y,AA where S='US History A'",0); QUERY('Reg Data'!A:AA,"select F,E,H,K,U,R,V,X,G,Z,AA where T='US History A'",0)},5,TRUE,6,TRUE,18,TRUE)

  28. I would like to figure out how to search by numbers. I have =QUERY(' Install PO Log'!B2:P,"SELECT * WHERE P = '72'",1) and I basically have jobs that are all assigned their specific numbers and I want to group the jobs by the numbers rather than by their name in order to simplify some other things for me. Is that possible? I can't figure it out, It only seems to work when I search by letters.

    For Example:
    This is my job list:

    Mcdonalds 1
    Arbys 2
    Wendys 3
    Arbys 2
    Arbys 2
    Mcdonalds 1

    I want to group them by searching by "2" instead of "Arbys" is that possible?

    1. Hello Jacob,

      When searching for numbers, do no put them into single quotes:
      =QUERY('Install PO Log'!B2:P,"SELECT * WHERE P=72",1)

      Only text strings should be wrapped in quotes in Google Sheets formulas.

      1. You just solved an issue I had been trying to solve for 6+ hours thank you so much!

        My next question is how I select certain columns. I have Columns A-P but I only need columns B,C,D,F,G,H,I,J,M,N,P. Right now, I have to hide the columns I don't need but that's very time-consuming when I have hundreds of these formulas to do. Is there any way to throw those specific columns into the formula so only they show? Below is the formula I am using now and I would just like to only use specific columns.

        =QUERY(' Install PO Log'!B2:P,"SELECT * WHERE P = 53",1)

  29. I would like to type a date into one cell... and have a query pull a column from another page that has dates in the top row (i'd like to search for the date here). Is this possible, and if so, what would the formula look like?

    1. Hi James,

      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 sample of your data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. 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.

  30. Hi, i have an issue and need help please :)
    When both query have numbers it's working but when one doesn't find a result i have N/A error, i can't find a way to solve that even with a "ifna"

    =IF(isblank(B9),,{QUERY(Dividendes_TFSA!$B$8:$F$55,"select F where B='"&B9&"' and C like '%March%'")+QUERY(Dividendes_RRSP!$B$9:$H$55,"select G where B='"&B9&"' and C like '%March%'")})

    Thank you very much.

    1. Hi Angelique,

      You can wrap each QUERY in IFNA so they return 0 if there's no numeric result, like this:
      =IF(ISBLANK(B9),"",IFNA(QUERY(A2:B5,"select B where B="&B9,0),0) + IFNA(QUERY(A6:B10,"select B where B="&B9,0),0))

  31. Hello, I am wanting to pull columns from a named range based on a cell value in the sheet I'm pulling the info into but I can't seem to figure out how to do it.
    I want columns D, O and P from the named range All2021Regos where C = my current spreadsheets C2.

    =Query(All2021Regos,"select D,O,P Where C=C2")

    =Query(All2021Regos,"select D,O,P Where C='Attendance Sheet auto'!C2",)

    I was hoping for something like this, but with a named range, and with having a cell determine the value rather than having it in the formula.
    =QUERY(IMPORTRANGE("insert_url","Range"),"Select Col1, Col2 Where Col4='Value'")

    Any help would defs be appreciated.

    1. Hello Sam,

      You can try this one:
      =QUERY(All2021Regos,"select D,O,P where C='Value'")

      Or, if you'd like to reference a cell, you need to let QUERY know that it's a reference, not a column name (I add 1 at the end, so it sees the header row):
      =QUERY(All2021Regos,"select D,O,P where C='"&C2&"'", 1)

  32. Hello, thanks for taking the time to write great blogs and helping us all so much.

    My issue is I am using query to select figures from a couple of columns (that are created each day and so generally differ in length) which relate to volumes of sales of different products. I can exclude the top row from being sorted alphabetically but the 'Totals' row at the bottom is swept up and included.
    If I call it 'zz Total' obviously it stays down where I want it to be but that's horrible - is there a way to exclude the final line from being included in the query search?

    1. Hello Nick,

      Thank you for the feedback! :)

      You can make the formula ignore the first and the last rows using the ArrayFormula and OFFSET functions respectively for the 'limit' command. Here's an example:
      =QUERY({Sheet10!A1:E}, "select * limit " &ArrayFormula(max(if(len(Sheet10!A1:E), ROW(Sheet10!A1:E), )))-2 & " OFFSET 1", 0)

      If you're not sure how to incorporate that into your own QUERY, please provide your exact formula here.

  33. Hi Natalia,

    I was hoping you can assist!

    Column H I J is where my datas are and I need to return the data if what I type in Column G matches column H — what is the best way to go about this?

  34. Hi Natalia, How can i find rows in a column(that has sentences as values) that dont contain a particular word?

    1. Hi Saumittra,

      Supposing it's a column A, the following formula will do the trick:
      =QUERY(A:A,"select * where not A contains 'particular_word'")

  35. is there a way to return the query results with bordered cells. I mean make cells that have results with borderes

    1. Hello Hassan,

      I'm sorry but standard Google Sheets formulas don't pull the formatting of the processed cells.

  36. Hi

    Great article, but I'm stuck and wondered if you can help, I'm Trying to get a (Where) query to return columns from sheet1 into sheet2 where the criteria to meet is in col1 in sheet2!

    An example from your article is 1 below, but I'm trying to achieve 2?
    1) =QUERY(Sheet1!A1:G11,"select A,B,C,F where F>=10")

    2) =QUERY(Sheet1!A1:G11,"select A,B,C,F where A = Sheet2!A2:A")

    Its a text based field, so if it were countries for example, I have a column of countries and I want to filter every row from sheet1 that corresponds with col1 in sheet2, every row line being unique.

    Any help is much appriciated.

    Thanks

    Sheet1!A2:A being the filter data that is in Col1 of both my sheets?

    1. Hi Ray,

      Thank you for your feedback!

      I'm afraid you'll need more functions than just QUERY:
      =QUERY(ARRAYFORMULA(IF(IFERROR(MATCH(Sheet2!A2:A10,Sheet4!$A$2:$A,0)),QUERY(Sheet2!$A$2:$G$10,"select *"),"")),"select * where Col1 is not null")

  37. Hello Natalia and the rest of the Ablebits Team.

    I'm trying to make a join query, where I combine data in the same cell.

    So let's say I have the same author talking about different subjects and his name is listed 3-4 times in column A, but with different subjects in column B. Then I want an overview where the query joins the subjects by the name of the author.
    So something like =join(", ",query(A:B,"Select A,B by A")) or something like that.

    And even further, also sums the profits by the authors subjects would be an awesome addition!

    Could you help me out? Thanks!

  38. Hello, I'm trying to use lowercase trimmed values in my select query, but am getting an error
    =QUERY({Original!A2:Q18909}, "select lower(trim(Col1)), count(lower(trim(Col1))) where Col1 is not null Group BY Col1 PIVOT Col2 ",0)

    The error reads #VALUE Unable to parse query

    Could you please point where I go wrong? Thanks!

    1. Hello Marina,

      There's no need to wrap the range in curly brackets unless you combine multiple datasets. Also, you refer to Col1 with the 'select' clause twice, this can be the problem.

      The best way for us to help you is for you to share an editable copy of the spreadsheet with us – support@apps4gs.com – we'll look into it. Make sure it contains the formula that causes problems. 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, please do not email there. Once you share the file, just confirm by replying to this comment.

  39. How to extract parts of data from one cell and display in different columns at the destination sheet using QUERY()?

    1. Hello Vinesh,

      For me to be able to suggest you anything specific, describe your task in detail: what do you have in a cell and what part do you want to pull? Do you pull data to a new tab of the same file or to another spreadsheet?

  40. Can you use Select where A begins with (A-Com) - or any range of letters. Trying to sort data to different tabs based on last names ranges.

    1. Hello Eric,

      If 'A-Com' is literally a string used at the beginning of some cells, you can get such cells using the 'where' label:
      =QUERY(A1:A50, "select * where A starts with 'A-Com'

      But if you mean it's a range of letters, you will have to list each letter separately, like this:
      =QUERY(A1:A50, "select * where (A starts with 'A') or (A starts with 'B') or A starts with 'C'")
      Note that all cases but the last one are put into brackets.

  41. How do you select data from the row being grabbed in the query to populate another portion of the table. If I run =QUERY(J:J, "where J=true") how do I get it to reproduce the data from those queried rows for columns B, C, and D? I haven't had the best luck with testing so far.

    1. Nevermind, I got it figured out. Thanks for the info on this page, it has been very helpful!

  42. Hi,

    On the Query function, is it possible to search under a specific column where instead of using the A,B or C column you will be using the Header name itself like County, Building,

    Because from my understanding if data from the columns A.B and C were to be moved to another column, then the queried data will also be changes as specific to what the column has.

    So instead I would like to have a specific column identifier?

    Thanks!

    1. Hi James,

      I'm afraid it's impossible. The QUERY function works with column IDs, not labels (column headers).

  43. Hi ?, I'm trying to search rows rather than columns using the Query function. I want to return the headers every time a I receive a 'No' answer in my data. This needs to be done for each individual row.

    Range A1:B10, select row A where B contains 'No'

    Or should I be using an array or index?

    Thanks!

    1. Hi Abbie,

      From what you described, it still looks like you're searching column B (not a row) for a specific value and want to return the corresponding value from column A if the value in B is found. If it's so, please check out Example 2 in this part of the article: Google Sheets QUERY – Where clause.

      If it's not what you mean, please try to explain your task in more detail. I'll do my best to help.

  44. Can I split one row with 10 columns data of marks into 5 rows and 3 columns, while importing data using IMPORTRANGE function.

  45. Hi,

    I woul like to learn about query more where X column has a exactly value, I see some examples when you use more than or less than but I don´t know how to use with exactly value,

    Thanks a lot !

  46. Hello,
    Is there anyway to show row number in a query?

    Thanks.

    1. Hello Alton,

      Yes, there is. Add the ArrayFormula function to the first argument, e.g.:
      =QUERY({ARRAYFORMULA(ROW(A1:G)), A1:G},"select * where Col1 is not null")

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 :)