How to return array of cells from a table in Google Sheets

Question

We have # EXAMPLE TABLE with product SKUS on the top row, and the first columns is ingredients. Each SKU row is showing the value of the ingredient row. What we want to do is have another sheet with a drop down, where a user can select from the various SKUs and then we have a formula (maybe =query?) that will dynamically show two columns; ingredients name and ingredient value for that SKU, when the ingredient value is greater than 0. I'd like to do this with a dynamic function like =query as opposed to nested =if.

# EXAMPLE TABLE

ingredients     cake        pie         quiche
flour           2           1           1
sugar           3           3           -
apples          -           2           -
berries         -           1           -
cheese          -           -           2
eggs            1           -           2

# EXAMPLE VIEW

Thing to make:   [ dropdown  ]

# USER selects from EXAMPLE VIEW

Thing to make:      [ cake ]    <---- user makes choice

# EXAMPLE VIEW returns:

Thing to make:      [ cake ]

flour   2
sugar   3
eggs    1

# or user selects   [ quiche ]

# EXAMPLE VIEW returns:

Thing to make:      [ quiche ]

flour   1
cheese  2
eggs    2

I've tried using =query'ExampleTable'!A:D, "select A where C > 0") and it will return the ingredient names where the values are larger than 0, so it would return an array [flour, cheese, eggs], which is very close, but not the full result I am seeking.

Answer 1

One approach is to filter the table before querying it. Here is an example where the table is in A1:D7 and the choice of product is in cell F1:

=query({A1:A7, filter(B1:D7, B1:D1 = F1)}, "select * where Col2 > 0")

Here, filter keeps only the column where the column name matches F1. The array notation {} makes a two-column array with the first column coming from A1:A7 (ingredient names) and the second being quantities. Finally, query selects the rows where the quantity is positive.

And this is a more decorated version of the above, which displays "no recipe found" when there are no matches, and omits the column names in the output by using label ... ''.

=iferror(query({A1:A7, filter(B1:D7, B1:D1 = F1)}, "select * where Col2 > 0 label Col1 '', Col2 ''"), "no recipe found")