Author: Oscar Cronquist Article last updated on October 11, 2018

Conditional formatting formula in B2:E5:

=IF(ROW(B2)>MIN(ROW(tbl)), COUNTIF(OFFSET(tbl, 0, 0, MIN(ROW(B2)-MIN(ROW(tbl))), MAX(COLUMN(tbl))), B2)+COUNTIF(OFFSET(tbl, MIN(ROW(B2)-MIN(ROW(tbl))), 0, 1, MIN(COLUMN(B2))), B2), COUNTIF(OFFSET(tbl, 0, 0, 1, MIN(COLUMN(B2))), B2))=1

Conditional formatting formula in B7:E10:

=IF(COUNTIF(tbl, B7)+IF(ROW(B7)>MIN(ROW(tbl1)), COUNTIF(OFFSET(tbl1, 0, 0, MIN(ROW(B7)-MIN(ROW(tbl1))), MAX(COLUMN(tbl1))), B7)+COUNTIF(OFFSET(tbl1, MIN(ROW(B7)-MIN(ROW(tbl1))), 0, 1, MIN(COLUMN(B7))), B7), COUNTIF(OFFSET(tbl1, 0, 0, 1, MIN(COLUMN(B7))), B7))=1, 1, 0)

Named ranges
tbl (B2:E5)
tbl1 (B7:E10)
What is named ranges?

Download excel example file.
(Excel 97-2003 Workbook *.xls)

Functions in this article

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

returns the row number of a reference

Counts the number of cells within a range that meet the given condition

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Returns the largest value in a set of values. Ignores logical values and text.

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

COLUMN(reference) Returns the column number of a reference

Returns the smallest number in a set of values. Ignores logical values and text