Forum Discussion
Formula that searches multiple rows of data and returns value if multiple cells within rows match
For example in G2:
=IF(COUNTIFS($A$2:$A$20000, $A2, $B$2:$B$20000, $B2, $D$2:$D$20000, $D2)>1, "DUPLICATE", "")
And in H2:
=IF(COUNTIFS($A$2:$A$20000, $A2, $B$2:$B$20000, "<>"&$B2, $D$2:$D$20000, $D2), "FLAG!", "")
Fill down.
For example in G2:
=IF(COUNTIFS($A$2:$A$20000, $A2, $B$2:$B$20000, $B2, $D$2:$D$20000, $D2)>1, "DUPLICATE", "")
And in H2:
=IF(COUNTIFS($A$2:$A$20000, $A2, $B$2:$B$20000, "<>"&$B2, $D$2:$D$20000, $D2), "FLAG!", "")
Fill down.
Thanks so much HansVogelaar!
The first formula is absolutely perfect!
On the second formula, is there a way to request a certain scenario specifically instead of just saying anything that is not B2 ("<>"$B2)?
I do have some scholarships that can both be posted for a student and I need those to be left alone. If I could have it say specifically ($B$2:$B$20000, "Presidential Sch"&"Music Sch",...) that way it's only looking for students with those two specific scholarships?
Again, cannot thank you enough!
That would be
=IF(AND(COUNTIFS($A$2:$A$20000, $A2, $B$2:$B$20000, {"Presidential S*","Music S*"}, $D$2:$D$20000, $D2)), "FLAG!", "")