# If this Date Range and This Cell Value Match?

#### thisishailstorm

##### New Member
Hello,
I am trying to highlight a collision if the date ranges in columns B and C coincide as well as the values in column F. The collision column G should then be either red, or indicate yes or an x.
How would I express this?

Thank you!

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

##### Well-known Member
you want only for consecutive rows or search at the total range?

##### Well-known Member
I suppose three condition:
1. Consecutive Rows
2. Non-Consecutive Rows with show Index at Column A for 2nd match
3. Non-Consecutive Rows with "YES"

Book1
ABCDEFGHI
1For ConsecutiveNot Consecutive
21BABANot RONo11YES
32BABARONo
43CRECRENot RONo12YES
54CRECRERONo
65CSCSNot RONo
76CSCSRONo
87INTDBTSERONo
98MAMARONo
109MAMANot RONo
1110BCPSRBCPHNNot RONo
1211BABANot RONo11YES
1312CRECRENot RONo12YES
Sheet1
Cell Formulas
RangeFormula
G2:G13G2=IF((B2=B3)*(C2=C3)*(F2=F3), "Yes", "No")
H2:H13H2=IFERROR(INDEX(\$A\$2:\$A\$13,SMALL(IF((B2=\$B\$2:\$B\$13)*(C2=\$C\$2:\$C\$13)*(F2=\$F\$2:\$F\$13),ROW(\$B\$2:\$B\$13)-ROW(\$B\$2)+1),2)),"")
I2:I13I2=IFERROR(IF(SMALL(IF((B2=\$B\$2:\$B\$13)*(C2=\$C\$2:\$C\$13)*(F2=\$F\$2:\$F\$13),ROW(\$B\$2:\$B\$13)-ROW(\$B\$2)+1),2),"YES","NO"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Replies
6
Views
180
Replies
17
Views
201
Replies
1
Views
130
Replies
3
Views
142
Replies
2
Views
99

1,172,024
Messages
5,878,783
Members
433,370
Latest member
mcleven

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back