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.