On MS XLS I can do some aggregate or leech off a structured reference, but I really don't know how to find the Nth result based on word match on Google Sheets.

I have huge columns - name, role. I need to pull out the first 10 people off any given role. Obviously, this can be done by sorting and filtering, but due to the non-stop changes to the list, it's better to get it by a formula that I can copy 10 times to get 10 matches.

Here is how to find the last match in case anyone is interested:


(a word you match, search range, result range)


=ARRAY_CONSTRAIN(QUERY(C5:D, "select D where C = '"&$C5&"'",0), 3, 1)


=ARRAY_CONSTRAIN(QUERY(C5:D, "select D where C = '"&"west"&"'",0), 3, 1)

to get first 10 matches

=ARRAY_CONSTRAIN(QUERY(C5:D, "select D where C = '"&$C5&"'",0), 10, 1)

  • Fancy stuff. I could always getaway way with not needing arrays in MS excel :) – helena4 Aug 20 '18 at 14:13
  • how do I pull out just specific Nth results? For instance just the 5th? – helena4 Aug 22 '18 at 11:24
  • like this: =INDEX(ARRAY_CONSTRAIN(QUERY(C5:D; "select D where C = '"&"west"&"'";0); 5; 1); 5) – MARK MY ANSWER Aug 22 '18 at 22:39

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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