Google-sheets – Conditional formatting to highlight Quiz Answers

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.

I dropped your formula into a dummy sheet and noticed one slightly unexpected behavior where it will flag a value of 0 (or a blank cell) as being the minimum value even in a column labeled with "No". If this is undesired behavior I'd recommend a slight tweak in returning a string to force an error if you don't want the data looked at in your if() function instead of an empty range (since the minimum value of an empty range seems to be considered 0).

(The use of ArrayFormula() is also not necessary here since MIN() is a function which can accept a range by default, but I've left it in since I'm not sure if you had another reason for wanting it.)

=ArrayFormula(E1=MIN(if(ISERROR(FIND("NO",E$1)),E$2:E$100,"Skip")))

So now onto your actual question. You've already mapped out all the logic for it in your post, you simply need to compare the difference between the minimum value and the current cell to .1, instead of directly comparing the two values.

=ABS(MIN(if(ISERROR(FIND("NO",E$1)),E$2:E$100,"Skip"))-E1)<.1

This will return a value of TRUE whenever the difference between the current cell and the minimum is less than .1, and FALSE otherwise.