Question: How do I highlight duplicate values in two ranges combined?

Answer:

Range 1, A1:D4, named tbl

Range 2, A6:D9, named tbl1

highlight-duplicates-in-two-ranges-combined1

Conditional formatting formula in A1:D4:

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

Conditional formatting formula in A6:D9:

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

Named ranges
tbl (A1:D4)
tbl1 (A6:D9)
What is named ranges?

Download excel example file.
highlight-duplicate-values-in-two-ranges-combined.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
returns the row number of a reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MAX(number1,[number2],)
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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text