SOLVED

# Formula that searches multiple rows of data and returns value if multiple cells within rows match

Copper Contributor

# Formula that searches multiple rows of data and returns value if multiple cells within rows match

I have a spreadsheet with 10,000+ rows of data within 5 columns (A, B, C, D, E). I need a formula that will check to see both of the following:

1. Does multiple cells in one row match exactly what any other row has? (Ie. Do cells A2, B2, and D2 together match any other row?) if so, then "Duplicate"

The example is listed below. There are multiple rows that have both the ID number and scholarship name but only row 1 and row 4 have matching ID number, scholarship name, AND season. I need the formula to be able to search all 10,000 rows in my spreadsheet and tell me if there are multiple rows that match in Columns A, B and D.

2. The next formula is to catch if someone has a two scholarships that conflict with one another.

Ie. someone cannot receive both a Presidential Scholarship and a Music Scholarship. I need a formula that will look through all 10,000 rows of these 5 columns and return "FLAG!" if an ID number in Column A match and has both the "Presidential SS" and "Music Sch" listed for the same season (in this instance "FALL").

So because A1 and A3 both have the same ID, B1 and B3 show both scholarships, and D1 and D3 both list the same season "FALL", I need G1 and G3 to both return "FLAG!"

Any assistance would be greatly appreciated!

3 Replies
best response confirmed by DeanPace (Copper Contributor)
Solution

# Re: Formula that searches multiple rows of data and returns value if multiple cells within rows matc

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.

# Re: Formula that searches multiple rows of data and returns value if multiple cells within rows matc

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!

# Re: Formula that searches multiple rows of data and returns value if multiple cells within rows matc

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!", "")

1 best response

Accepted Solutions
best response confirmed by DeanPace (Copper Contributor)
Solution

# Re: Formula that searches multiple rows of data and returns value if multiple cells within rows matc

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.