Search code examples
google-sheetsfiltergoogle-sheets-formulagoogle-sheets-querygoogle-query-language

What method can filter two pairs of data using filter() or other recommanded way?

enter image description here

I want to use filter() to search from B2:B corresponding to the range like F2:F3

I use =filter(A2:A,B2:B = F2:F3),but there is just one data Jack

I know this example can using FILTER to do the same thing like this. enter image description here

But now if there is a lot of data, it may not meet my needs.

Then I use =ArrayFormula(filter(A2:A,B2:B = F2:F3))

And it's not useful, too. (I don't know how to use arrayformula() methods)

What should I fix the formula and parameters?

Solution

  • Use COUNTIF to check whether the values B2:B are included in the range F2:F3:

    =FILTER(A2:A,COUNTIF(F2:F3,B2:B))

    enter image description here