I have only started to learn Google Sheets instead of Excel. I find it easier to understand if I have a problem to solve but the Query function quite difficult to understand.

I have table of fruit orders (quantity), the fruit labels on top row and customers names in the rows of left column.

With the customers names in a drop-down box I want the Query function to create another table with the fruit in the rows of the first column and quantity in the second column but ignoring the fruit that has zero or empty entries.

Currently I'm using the TRANSPOSE function to transfer fruit labels into the rows of the first column and I use the VLOOKUP function to extract the quantity of the chosen customer. Then I use filter on the quantity to remove the zero or empty entries

Please see my Sample sheet here: https://docs.google.com/spreadsheets/d/1vyh3gdOr6wugxH5CQtV4B--1K0_gghWI-ou7e0PZ6yY/edit?usp=sharing


1 Answer 1


Thank you Adam D. PE Diamond Product Expert for answering on Google forum.

QUERY(TRANSPOSE({B1:E1;QUERY(A:E,"select B,C,D,E where A='"&G1&"'",0)}),"where Col2>0",0)

This will first query the data find the information for the specific individual and then does a second query to eliminate the 0's as well as adds in the names of the items.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.