Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-query-language

Query formula Multiple Columns using "Where", "AND", and Torow

I have multiple sheets in data google sheet, let's both named is sheet 1 and sheet 2. In sheet 1, I have three columns as below:

enter image description here

And in sheet 2, I have three columns in below:

enter image description here

If I want to filter sheet 1 based column ID Primary, example the ID Primary is 54f94c2f. If i paste the ID Primary to column ID Primary (sheet 2), then on sheet 2 it will output the column as follows:

enter image description here

i have created formula for column 2 of sheet 2 (B2) and it is not worked. the formula is:

=torow(unique(filter(Sheet1!B:B,Sheet1!A:A=A2)),1)

The result is (still wrong):

enter image description here

The question is how the formula to fix it? Please help me.

Solution

  • Alternate Answer

    As the question states in the subject, here's a way to QUERY() using "WHERE", "AND" and TOROW() formula

    Try:

    =TOROW(QUERY(Sheet1!A2:C,"Select B where A='"&A2&"' and B is not NULL"))

    Result:

    enter image description here