Highlighting Incorrect Dependent Text in Google Sheets

If you’re using Google Sheets and want to easily identify and highlight incorrect dependent text strings, I’ve got just the formula for you! With a simple conditional formatting method called ‘format rule,’ you can quickly spot any misaligned text in your spreadsheet.

Understanding the Concept

To better explain this topic, let’s consider a table that lists countries and their corresponding capitals. In this table, the capital values in column B are dependents of the country names in column A. For instance, “Canberra” is the dependent of “Australia.”

Now, imagine someone accidentally enters “Sydney” instead of “Canberra” against “Australia.” We want to highlight “Sydney” as it is an incorrect dependent.

Using the ‘Format Rule’ Formula

To achieve this, we can use either VLOOKUP or MATCH formulas in conditional formatting. Let’s start by creating a table with proper alignment similar to the example given above.

Here’s how you can highlight incorrect dependent text strings using these two methods:

Method #1: VLOOKUP

1. First, search for the value in cell A2&B2 (Sheet1) within the range Sheet2:A2:A11&Sheet2!B2:B11. You can use the following VLOOKUP formula for this step:

   =ArrayFormula(vlookup(A2&B2,Sheet2!A2:A11&Sheet2!B2:B11,1,0))

2. Since cross-referencing is not directly supported in Google Sheets’ conditional formatting, we need to use the INDIRECT function. Modify the formula as follows:

   =ArrayFormula(vlookup(A2&B2,indirect("Sheet2!A2:A11")&indirect("Sheet2!B2:B11"),1,0))

3. By default, this formula will return #N/A if there is no match between the values. We want to highlight these incorrect dependents. To do so, use the IFNA function:

   =ifna(ArrayFormula(vlookup(A2&B2,indirect("Sheet2!A2:A11")&indirect("Sheet2!B2:B11"),1,0)),TRUE)

4. However, we need to apply this format rule to the entire column range in Sheet1, not just cell B2. To address this, use the AND operator to check if the cell in column B is not blank and if the VLOOKUP formula returns TRUE:

   =and(len(B2),ifna(ArrayFormula(vlookup(A2&B2,indirect("Sheet2!A2:A11")&indirect("Sheet2!B2:B11"),1,0)),TRUE)=TRUE)

Now, you can use this format rule to highlight incorrect dependent text strings in Google Sheets!

Method #2: MATCH

Alternatively, you can utilize the MATCH formula to achieve the same result. Here’s the formula for implementing this method:

=AND(LEN(B2),ifna(ArrayFormula(match(A2&B2,indirect("Sheet2!$A$2:$A$11")&indirect("Sheet2!$B$2:$B$11"),0)),TRUE)=TRUE)

In this formula, the first part returns #N/A if there is no match. The rest of the steps are similar to the VLOOKUP method. Choose the one that suits your preference.

Implementing the Format Rule

Now that you have the format rule ready, it’s time to apply it to your spreadsheet. Just follow these simple steps:

1. Copy the format rule you prefer.
2. In Sheet1, select the range B2:B1000 or the number of rows you want to apply the format to.
3. Click on “Format” and select “Conditional formatting.”
4. Make sure the “Apply to range” field is set to B2:B1000 or the selected range. If not, correct it.
5. Under the “Format rules” section, choose “Custom formula is” and paste the copied formula into the blank field.
6. Select the desired highlight color (e.g., light yellow) and click “Done.”

Congratulations! You have successfully highlighted incorrect dependent text strings in Google Sheets.

If you want to learn more about Google Sheets and other useful tips, visit Crawlan.com for expert guidance and helpful resources.

That’s all! Enjoy exploring the powerful features of Google Sheets.