CODEWITHSUNDEEP
google-sheets
The Singularity
The Singularity

Reputation: 2698

Combining VLOOKUP() Functionality with QUERY() in Google Sheets

I'm working with the following Google Sheet.

Sheet1 consists of the following query functions:

=TRANSPOSE(QUERY(IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!C8:N"), "Select Col10+Col11+Col12 where Col1 = 'A' label Col10+Col11+Col12 ''"))

=TRANSPOSE(QUERY(IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!C8:N"), "Select Col10+Col11+Col12 where Col1 = 'B' label Col10+Col11+Col12 ''"))

The above query functions return the sum of row values of Columns L, M, N from Sheet2 for products A and B

I would like to have the returned values matched with their respective Date and 0 returned if no entry for A or B exists on Sheet2 for that Date.

Please Advise.

Upvotes: 0

Views: 720

Answers (1)

Aresvik
Aresvik

Reputation: 4620

Using pivot within query will give you a similar result to your expected output on your sample sheet (I've removed the importrange on the test example):

=Arrayformula(query({Sheet2!C8:C,Sheet2!D8:D,Sheet2!L8:L+Sheet2!M8:M+Sheet2!N8:N},"select Col1,sum(Col3) where Col1 is not null group by Col1 pivot Col2 label Col1 'Products' ",0))

enter image description here

The pivot data is formatted 'yyyy-mm-dd' but if you want it as entered on Sheet2:

=Arrayformula({"Products",transpose(sort(unique(query({Sheet2!D8:D},"where Col1 is not null",0)),1,1));query(query({Sheet2!C8:C,Sheet2!D8:D,Sheet2!L8:L+Sheet2!M8:M+Sheet2!N8:N},"select Col1,sum(Col3) where Col1 is not null group by Col1 pivot Col2 ",0),"offset 1",0)})

enter image description here

Where there is 0 values on a given date the result is 0. Where there are no dates for A or B, the result is blank. The empty values are not quite what you asked for but they do indicate where there are no dates in the data.

Both examples with the importrange added back in:

=Arrayformula(query({IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!C8:C"),IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!D8:D"),IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!L8:L")+IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!M8:M")+IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!N8:N")},"select Col1,sum(Col3) where Col1 is not null group by Col1 pivot Col2 label Col1 'Products' ",0))

and:

=Arrayformula({"Products",transpose(sort(unique(query({IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!D8:D")},"where Col1 is not null",0)),1,1));query(query({IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!C8:C"),IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!D8:D"),IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!L8:L")+IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!M8:M")+IMPORTRANGE("1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ", "Sheet2!N8:N")},"select Col1,sum(Col3) where Col1 is not null group by Col1 pivot Col2 ",0),"offset 1",0)})

It might be more manageable to reference the URL from a cell or named range in your sheet, eg:

IMPORTRANGE(A1, "Sheet2!D8:D")

where A1 is the cell containing 1JIAydWLj85vtcSkqNjINv9Ab5n7aX8y9XBY5fP-NQSQ.

Upvotes: 2

Related Questions