1

How do I use the query in Google Sheets to return N results from each tab.

So I have this formula:

=QUERY({sheet1!A2:D;sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3")

But what I want is that the results to be limited to 3 but from each individual sheet tab so I have displayed 3 results from sheet1 and 3 from sheet2.

1
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. – marikamitsos Mar 2 at 14:58
0

Use the following

={QUERY({sheet1!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3"),
  QUERY({sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3")}

(do adjust syntax according to your locale)

2
  • ok i see that i have just two questions, 1. I want the results overall to be sorted by Col4 as I see this formula it will display first sheet1 and order by col4 then sheet2 on order by col4 it will not be an overall sort but by sheet by sheet ; 2. is this the only option ? because I listed here 2 sheets but I may need to use a lot more so it will get kinda to a long formula. Thanks for your answer. – Maihes Mar 2 at 16:57
  • Oh and i just found out another problem, if for example any of the sheets has no data to be pulled then I get an error on the main sheet "#VALUE! ( In ARRAY_LITERAL, an Array Literal was missing values for one or more rows. )" – Maihes Mar 2 at 19:01
1

Well hello, after much trial and error I managed to get a formula that does exactly what I want there is just one simple problem it's so long .... but maybe it can help others too or someone can come up with a way to simplify it.

=SORT(IFERROR({iferror(query({sheet1!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3";0);A2:D2/0); iferror(query({sheet2!A2:D}; "select * where Col4>=65 order by Col4 desc Limit 3";0);A2:D2/0)});4;0)

This formula will display 3 results from each tab like I wanted and will sort it overall all the data displayed but like I said it's very long already and if I want to add more tabs it will get even longer. But it's the only solution I could come up with that does everything I needed automatically. I hope it can help people and if there is someone who can simplify it that would be an even greater help.

1
  • Glad you worked it out. Kudos. Remember you can also accept your own answer. – marikamitsos Mar 5 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.