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) – user0 Aug 22 '18 at 22:39

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.