I have an array query that runs across multiple sheets in a Google Doc. The query works fine, but I would like to know the sheet of each data returned. I made an example HERE . the formula is :
=QUERY({Sheet2!A2:B6; Sheet3!A2:B6}, "select Col1, Col2 where Col2 = 'Y'")
When a 'Y' is found, I want to return the Data in Col1, Col2 AND (this is the part I don't know how to do) the value of A1 on the page where the match occured.
You can insert page title in the array as its third column, and select that column with the query:
=arrayformula(query({Sheet2!A2:B6, iferror(Sheet2!A2:A6/0, Sheet2!A1); Sheet3!A2:B6, iferror(Sheet3!A2:A6/0, Sheet3!A1)}, "select Col1, Col2, Col3 where Col2 = 'Y'"))
Here, iferror(Sheet2!A2:A6/0, Sheet2!A1) is a clumsy but effective way of saying: take the shape of array Sheet2!A2:A6/0 and fill it with the content of the cell Sheet2!A1. This results in the page title being repeated in every row taken from that sheet (provided that arrayformula wrapper is present, so that the computation /0 is performed with the array). The query returns
Data 2 on page 2 Y Page 2 Title
Data 4 on page 2 Y Page 2 Title
Data 2 on page 3 Y Page 3 Title
