Search code examples
google-sheetsgoogle-sheets-formula

Query two sheets and output on third

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)
enter image description here

Sheet2 (there are over 100 different)
enter image description here

Sheet3 (expected output of Sheet1 using terms from Sheet2)
enter image description here

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)")

Demo sheet

Solution

  • result

    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.