Find range of cells within table

[Vshurden]

I have data in e3:g3, I want to know if that same range of data is repeated in any row in a3:c150. Is there a formula to do this?

 

[Rijnsent]

Hi Vshurden,

the formula for H3 (assuming you have your check formula there) would be:
=COUNTIFS(A$3:A$150,E3,B$3:B$150;F3;C$3:C$150;G3)
If that formula shows a 1, there is a match somewhere, if it's >1 there are multiple matches, if it's a 0, you have no matches.
Hope that helps,

Koen

 

[Rick Rothstein]

Hi Vshurden,

the formula for H3 (assuming you have your check formula there) would be:
=COUNTIFS(A$3:A$150,E3,B$3:B$150;F3;C$3:C$150;G3)
If that formula shows a 1, there is a match somewhere, if it's >1 there are multiple matches, if it's a 0, you have no matches.

You mixed you delimiters for the function... your first two delimiters are commas but your last three are semi-colons. Assuming commas throughout, your formula would be...

=COUNTIFS(A$3:A$150,E3,B$3:B$150,F3,C$3:C$150,G3)

 

[Rijnsent]

Hi Rick,
good one, my settings are semi-colons and I generally try to change them to commas when posting here (as it seems the default), but did a sloppy job this time (manual labor, will use search&replace next time),
Thanks for pointing it out,
Koen