## Highlight duplicate values in two cell ranges combined

*Article updated on December 22, 2017*

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

**Answer:**

Range 1, A1:D4, named tbl

Range 2, A6:D9, named tbl1

Conditional formatting formula in A1:D4:

Conditional formatting formula in A6:D9:

**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

### One Response to “Highlight duplicate values in two cell ranges combined”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

If you move the second table one column to thr right the first occurance of the duplicate highlights incorrectly, and different from that shown when the data starts in the 'A' column.

Regards

Dave