Google-sheets – Conditional formatting of a row header based on the values in the column

Assuming the actual finishing date is in Column F and the sheet is called Sheet1, paste the code below inside your spreadsheet under Tools >> Script editor, then hit Save.

/***
* Color Code+ 
* Conditional formatting with cell referencing for Google Spreadsheets
* Support: scripts@bryanp.com
**/

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
var values1Rule1 = s.getRange('E1:E').getValues();
var values2Rule1 = s.getRange('F1:F').getValues();
var range3Rule1 = s.getRange('E1:E');
var color1 = 'red';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] == values2Rule1[row][col]) s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor('white'); }}
};

I used this script generator to make creating the code easier. You can adapt it to your needs.

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")