Google-sheets – Using Conditional Formatting to highlight data in new table that matches data in separate table

conditional formattinggoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

Sheets 1 thru 6 all have an Array Formula pulling data from other sheets. The Array Formula runs multiple Query formulas that skip over rows with missing cell data and then goes back to those rows and add them to the new table with an alternative column to fill in the missing data.

Example:

The sheet '5 Star Gear Sets' starts to fill in column E with data that may or may not be present in column F of the reference sheet, if the data is not present, it fills in column E with data existing in column G of the reference sheet instead.

Trying to Accomplish:

I'm trying to use Conditional Formatting to Highlight all cells in the E column who's data was filled in by the data of column G from the reference sheet.

Current Formula:

Apply to Range: E6:E1001

Format Cells If…: Custom Formula

=MATCH(
    E6,indirect(
        "'5 Star Gear Sets (Hidden)'!E6:E"
    ),0
) 
=MATCH(
    B6,indirect(
        "'5 Star Gear Sets (Hidden)'!A6:A"
    ),0
)

The Problem:
It works until there is a repeat number in column E. It fails to highlight any cell with a number that has already been highlighted in column E.

Link:

Below is an updated copy of the spreadsheet in question

https://docs.google.com/spreadsheets/d/1HzmDJtYun_vqVvV1HKys3UNxIfyrsA4imcjw8PcoSGQ/edit?usp=drivesdk

Best Answer

EXPLANATION OF BAD FORMULA

=MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0) =MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)

The extra formula starting with a second = is the same as wrapping the entire formula with AND( , ).

=AND(MATCH(E6,indirect("'5 Star Gear Sets (Hidden)'!E6:E"),0),MATCH(B6,indirect("'5 Star Gear Sets (Hidden)'!A6:A"),0)

This Formula does not work because it checks to see if E6 matches any cell in E6 thru E of the referenced sheet and then also checks if B6 matches any cell in A6 thru A of the referenced sheet. Once it finds the first match in the reference sheet, it does NOT use the same row in the range of the second MATCH function.

=MATCH(search_key, range, [search_type])

Example: =MATCH(A6,B1:B100,0)

Using MATCH in a Conditional Format will return True or False.

DISCLAIMER

I wrote the answer for a different sheet than the one listed in the original question. If using link provided in the question for the spreadsheet, the answer is meant to be applied to what would normally be considered Sheet1.

ANSWER

Apply to Range: E6:E

Format Cell if...: Custom Formula

Formula: =AND(NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6),E6<>"")

Formating Colors Text Color: None

Background Color: Light Red

=FILTER(range, condition1, [condition2], ...)

Example: =FILTER(A1:A100,B1:B100=C1)

The FILTER( , ) function needs to be used instead of the MATCH( , , ) function. FILTER( , ) references the cell(s) from the range whos row(s) matches the condition(s).

=INDIRECT(cell_reference_as_string, [is_A1_notation])

Example: =INDIRECT("Sheet2!A1:B")

The INDIRECT( ) function has to be used because the reference sheet can not be referenced directly.

=INDIRECT("'6 Star Gear Sets (Hidden)'!A6:A")=B6

=B6 has to be added to condition1 so that only the value in the range are referenced whos row matches the condition1. This finds which cell in range A6 thru A in the reference sheet matches B6. Only 1 cell is returned because A6 thru A in the reference sheet are unique.

=INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6

$ needs to be added in front of the row in both the range and condition in order for them NOT to change as the Conditional Format Rule goes down each cell to apply the condition to. (Something I failed to do in the original formula.)

=FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)

Add the FILTER( , ) function and the range. '6 Star Gear Sets (Hidden)'!D$6:D returns the contents of D6 thru D of the referenced sheet that is on the same row as 6 Star Gear Sets (Hidden)'!A$6:A found in condition1.

=FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6

=E6 has to be added to the end of FILTER( , ) so that the formula checks to see if E6 matches the resulting contents of the cell found from D6 thru D of the referenced sheet. Because it is a Conditional Formating Rule, the result of the FILTER is either True or False, thus applying the Format if it is True and NOT applying the Format if it is False.

=NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6)

=NOT is added because we want True to actually be False and False to actually be True. We are trying to Format the cells that are not True of the FILTER( , ) we are using.

=AND(NOT(FILTER(INDIRECT("'6 Star Gear Sets (Hidden)'!D$6:D"),INDIRECT("'6 Star Gear Sets (Hidden)'!A$6:A")=B6)=E6),E6<>"")

=AND( , ) and E6<>"" has to be added so that it does NOT format every cell in column E that is blank. =AND( , ) in this case states that not only does the FILTER( , ) must be true, but also E6<>"". E6<>"" just states that E6 is not BLANK.

Related Topic