I have two Google sheets. Sheet1 are the employees, Sheet2 are the job titles, and Sheet3 I would like to find all employees with the specified job titles.

Sheet1 (thousands of rows)

Sheet2 (there are over 100 different)

Sheet3 (expected output of Sheet1 using terms from Sheet2)

Trying to figure it out and so far no luck trying something like =query('Sheet1'!1:100000, "Select \* Where LOWER(F) = LOWER('Sheet2!'2:300)")

CodePudding user response：

try this:

=ArrayFormula(QUERY({$A$1:$C,XLOOKUP($B:$B,sheet2!$A$2:$A$8,sheet2!$A$2:$A$8,"")},
"SELECT Col1,Col2,Col3 WHERE Col4 IS NOT NULL",1
))

You can always pre-process the data range of a query.