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")
It's difficult to envision why you would merge entire columns A:B, since that would essentially just create one column; so I'm guessing that only the header columns are merged. If that is the case, there is a simple solution: include at least five blank columns from Data! in your QUERY.
For instance, if you only had data in Data!A:E (i.e., F and following are blank), you could structure your QUERY in Query!A1 like this:
=QUERY(Data!A:J,"Select A, F, B, G, C, H, D, I, E, J Limit 100")
Notice that the Select calls filled column, empty column, filled column, empty column, etc.
However... if you were merging header columns in Query! and trying to fill the left-hand column under each merged header while leaving the right-hand column under each header free to add new data, this approach will not work, since you can't add manual data anywhere inside the scope of a QUERY-filled range.
In that case, the simple solution is simply to place five separate QUERY calls:
In A1: =QUERY(Data!A:A,"Select * Limit 100")
In C1: =QUERY(Data!B:B,"Select * Limit 100")
In E1: =QUERY(Data!C:C,"Select * Limit 100")
In G1: =QUERY(Data!D:D,"Select * Limit 100")
In I1: =QUERY(Data!E:E,"Select * Limit 100")
Best Answer
Use the following
(do adjust syntax according to your locale)