I apologize if this has been asked before, I tried searching but couldn’t find exactly what I’m looking for. I want to highlight rows in Google Sheets using conditional formatting if two different rows have duplicate data in column E, and they also have duplicate data in column F. I’m currently using =AND(countif($E:E,$E1)>1,countif($F:F,$F1)>1) which seems to work most of the time, especially if there are several rows that meet the criteria, but the problem is it is still highlighting a row if there is a duplicate anywhere in column E, and anywhere in column F, even if the duplicates are in different rows. Here is a link to a sample sheet where the problematic behavior is shown. In this example sheet, no row should be highlighting right now (if it is working the way I intend), since the duplicate data is not in the same row. If it is working the way I intend, it should never highlight only one row.
https://docs.google.com/spreadsheets/d/10LvijhfsvMKUDNwCQB9GsiEG-pjO87cqtpialoB4YNA/edit#gid=0
>Solution :
try:
=INDEX(COUNTIF($E:$E&$F:$F, $E1&$F1)>1)*($E1<>"")