Duplicate value search

PaulJ

Board Regular
Joined
Dec 19, 2011
Messages
61
Can some one please provide a formula to allow me to identify duplicates within a specific range.

I have a value in a cell H70 which is text that is selected from a drop down list. I want to know if that value has also in another cell (H4 for example). I would like the cell fill in H70 to go red if that value occurs in cell H4 through H22 inclusive.

Thank you for your patience.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,039
Office Version
365
Platform
Windows
Select cell H70 and ..
click on Conditional format (Home tab) \ New Rule \ Use formula to determine cell to format \ enter formula below in the box \ click on Format \ etc ..

=COUNTIF(H4:H22,H70)>0
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,684
Office Version
365
Platform
Windows
=COUNTIF(H4:H22,H70)>0
FWIW the >0 is not required as this will do just as well
=COUNTIF(H4:H22,H70)

A similar result can be obtained with this CF formula
=MATCH(H70,H4:H22,0)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,353
Messages
5,468,116
Members
406,566
Latest member
MsfStl

This Week's Hot Topics

Top