I have a table I'm using to list recipes and their ingredients. Ingredients are rows, recipes are columns, with a column next to the ingredients denoting if that ingredient is in stock.

Like so:

Ingredient | Stock | Crab Cakes
Crab       |  Yes  | 12 oz
Mayonnaise |  No   | 1/4 cup
Jalapenos  |  Yes  |
Egg        |  Yes  | 1
Water      |  Yes  |

What I would like to do is create a conditional format that checks if an all ingredients listed in a row are in stock, and if so, changes the color of the row header.

It feel like it should be fairly simple, but I am at a loss.

1 Answer 1


Apply to the headers (C1 and the following) conditional formatting based on custom formula


Here, FILTER selects the rows that satisfy both conditions: igredient is not in stock, and ingredient is used in the recipe. This command returns the error #N/A if no results are found. The command ISERROR returns True if its input is an error. As a result, the formula evaluates to True provided that all ingredients used are in stock

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.