Query two sheets and output on third

Question

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

Demo sheet

Do you means that you want to output only Employee data with Job Tiltles in the list of Sheet 2, right? — Ping
– Ping, Commented Nov 10, 2022 at 4:00
@Ping Sheet1 has the data, Sheet2 has the search queries. Query Sheet1 using Sheet2 and display on Sheet3 — Peter
– Peter, Commented Nov 10, 2022 at 14:49

Harun24hr · Accepted Answer · 2022-11-10 04:01:10Z

1

Try below query-

=QUERY(Sheet1!A:C,"select * where B matches '" & TEXTJOIN("|",TRUE,Sheet2!A2:A) & "'",1)

answered Nov 10, 2022 at 4:01

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Harun24hr Over a year ago

FILTER() with COUNTIFS() should also work.

Peter Over a year ago

Thanks. It works. But is there a limit on TEXTJOIN? My query works at =QUERY(Sheet1!A1:G17430,"select * where F matches '"& TEXTJOIN("|",TRUE,Sheet2!A2:A183) &"'",1) . Have 231 rows in Sheet2

Harun24hr Over a year ago

Yes. There is limit. See this post please. support.google.com/docs/thread/90475863/…

Harun24hr Over a year ago

If limit exceed then we have to go with FILTER().

Ping · Accepted Answer · 2022-11-10 04:21:20Z

0

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.

answered Nov 10, 2022 at 4:21

Ping

9111 gold badge5 silver badges10 bronze badges

4 Comments

Peter Over a year ago

This would only work within the same sheet but I am trying to use data from Sheet2 to Query Sheet1 then display the results on Sheet3

Ping Over a year ago

... there is no difference, you can name the reference range to whatever you like. I think you do know the basic of how excel and spreadsheet reference range works, right? I put the result in the same page is because it is easier to compare with the original data.

Ping Over a year ago

This is as simple as giving the reference range a sheet name.

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

Peter Over a year ago

Thanks. I noticed the issue that was stopping mine from working

Collectives™ on Stack Overflow

Query two sheets and output on third

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related