I have two lists (A and B). I want to filter list "A" (a multi column list) with values in list "B" to create a Final List, list "C".

List "A" has multiple records for each student ID number. List "B" has a list of student ID numbers I want to include in list "C". I need all references to student ID Numbers in List "B" to appear in List "C" and I want all references to student ID numbers listed in list "A", but not in list "B" to be removed from List "C".

List "A" Structure

ID #    First   Last     Class
---     ---     ---      ---
123     Tim     Jones    Math
123     Tim     Jones    English
111     Amy     Smith    Science
111     Amy     Smith    GYM
222     Mike    Young    Math
222     Mike    Young    English

List "B" Structure

ID #    

Desired List "C" Structure

ID #    First   Last     Class
---     ---     ---      ---
123     Tim     Jones    Math
123     Tim     Jones    English
222     Mike    Young    Math
222     Mike    Young    English

I'm working with this formula, but I'm not sure how to structure the the part after this, where Col1 = nor am I positive it will work.

=query(importrange("1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE","data!A2:F"), " Select * where Col1 ='"&A2&"' ",1)

I've also tried the below based on ttarchala answer, but it is only returning one row (ID#327288).

=filter(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "data!A2:2"), match(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "data!A2:2"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "myStudents!A2:A"), 0))

Any help is greatly appreciated. Thank you!

2 Answers 2


Use a FILTER/MATCH combination. Example on your data below:

=filter(C52:F57; match(C52:C57; I53:I54; 0))

enter image description here

  • Hey @ttarchala! Thanks for offering help. I'm working with your example and have written this formula, but it isn't working. Can you take a look and let me know if you can see what I'm missing? =filter(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "data!A2:2"), match(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "data!A2:2"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "myStudents!A2:A"), 0))
    – Mr. B
    Feb 7, 2018 at 18:46
  • 1
    What error are you getting? I can't test your formula without permission to access your spreadsheet. If all else fails, put the results of importranges in separate sheets locally and filter from there.
    – ttarchala
    Feb 8, 2018 at 8:17
  • =filter(data!A2:F, match(data!A2:A, myStudents!A2:A, 0)) @ttarchala, this worked. Thanks for your help!
    – Mr. B
    Feb 8, 2018 at 16:49

@ttarchala provided a very helpful answer. If you need to invert the selection you can simply do it like this:

=filter(C52:F57; ISNA (match(C52:C57; I53:I54; 0)))
  • Downvote was unnecessary, so upvote from me!
    – user15741
    Oct 1, 2020 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.