Match Available and Required Items and Highlight in Google Sheets

Imagine you’re a pharmacist. A customer comes to you with a prescription and wants to buy all the items on the list from you. If any item is unavailable, they’ll go to another pharmacist.

To keep track of your stock in Google Sheets and identify if all the required items are available, you can use conditional formatting. With this feature, you can highlight the entire record when all the medicines in the prescription match the available stock. If there’s a nonavailable item, it will be highlighted in red. This article will guide you on how to achieve this in Google Sheets.

Match Available and Required Items (Ingredients) and Highlight Entire Row

Let’s start with horizontally arranged data as an example. In the table below, we have the names of ingredients in row #1 and checked (tick marked) available ingredients in row #2. Rows 3 and 4 represent two recipes: “Cauliflower Cheese” and “Paneer Masala Fry.”

In this example, we can prepare “Paneer Masala Fry” because all the ingredients are available. However, for the “Cauliflower Cheese Recipe,” the ingredient “green chili” is not available.

To match the available and required items (ingredients) and highlight the entire row, follow these steps:

1. Select the range B3:R100.
2. Go to Format > Conditional formatting.
3. Make sure the active tab is “Conditional format rules” and choose “Single color” as the rule type.
4. Select “Custom formula is” under Format rules.
5. Copy and paste the following formula as Rule_1:

   =and($A3<>"",countif(ArrayFormula($B$2:$R$2&$B3:$R3),"FALSEok")=0)

6. Click “Done.”
7. To insert Rule_2, click “Add another rule” and choose a different color (preferably red) to highlight nonavailable values/items/ingredients.

Formulas (Highlight Rules) Explanation

There are two rules in play here, so let’s explain them one by one.

Rule_2 Explanation

Rule_2 highlights nonavailable items (ingredients). The formula =and(len($A3),B$2=FALSE) checks if the tick box is unchecked (FALSE) in B2:R2 and highlights the entire column. Here are a few things to consider:

• The len($A3) checks A3:A100 to ensure that the rule won’t highlight blank rows.
• The conditional format gives priority to Rule_1. If Rule_1 highlights an entire row, Rule_2’s highlighting won’t be visible.

Rule_1 Explanation

Rule_1 matches available and required items and highlights the entire row if all the required items are available. The formula =and($A3<>"",countif(ArrayFormula($B$2:$R$2&$B3:$R3),"FALSEok")=0) combines the availability (B2:R2) with the requirements (B3:R3, B4:R4, B5:R5, and so on) in each row. Here’s how it works:

• If any cell has the value “FALSEok,” it means the item (ingredient) is required for the recipe but not available.
• The COUNTIF on this array result returns 0 if there are no “FALSEok” values, indicating that all required items are available.
• The formula uses absolute (fixed) references for $B$2:$R$2 and relative references for $B3:$R3.

Match Available and Required Items (Ingredients) and Highlight Entire Column

While horizontal data is more reader-friendly for matching available and required items in Google Sheets, some prefer a vertical orientation. Here’s an example of vertically arranged data:

Please note that I recommend using horizontal data for this purpose. However, if you prefer vertical data, you can modify the formulas accordingly. The formulas for Rule_1 and Rule_2 in this case are:

Rule_1: =and(C$2<>"",Countif(ArrayFormula($B$3:$B$19&C$3:C$19),"FALSEok")=0)

Rule_2: =and(len(C$2),$B3=FALSE)

Apply these rules to the range C3:19.

That’s it! You now know how to match available and required items in Google Sheets and highlight them using conditional formatting. For more tips and tricks, visit Crawlan.com. Enjoy exploring the possibilities!