Matching if cells in a given range match any value in another range

[surkdidat]

I ned to know if any cells (excluding blanks) in H2 to J2 match any cells in R2 to T2 (again excluding blanks). If just one (or more) with a value match, I would like it to return "MATCH", or "NO MATCH" if not

H2 - Peter	I2 - Dave	J2 - Steve	R2 - Andrew	S2 - Peter	T2 - Nathan	U2 - MATCH
H3 - Sophie	I3 -	J3 -	R3 - Ellie	S3 -	T3 -	U3 - No Match
H4 - Andy	I4 - Sophie	J4 -	R4 - Andy	S4 - Sophie	T4 - Dave	V3 - MATCH

 

[Fluff]

How about

=IF(SUM(COUNTIFS(H2:J2,R2:T2)),"Match", "No match")

 

[surkdidat]

How about

=IF(SUM(COUNTIFS(H2:J2,R2:T2)),"Match", "No match")

Hi Fluff, does that exclude blanks (I probably should have also mentioned that the "blanks" are a result of an INDEX-MATCH formula not finding a value so its returning a blank due to IFERROR being "")

=IFERROR(INDEX(Sheet1!$C$2:$C$11841,
MATCH(1,
((Sheet1!$D$2:$D$11841=$O2)*
(Sheet1!$G$2:$G$11841=R$1)*
(Sheet1!$N$2:$N$11841>=$Q2)*
(Sheet1!$M$2:$M$11841<=$Q2)),0)),"")

 

[Fluff]

In that case try

=IF(SUM(COUNTIFS(H2:J2,R2:T2,H2:J2,"?*")),"Match", "No match")

 

[surkdidat]

In that case try

=IF(SUM(COUNTIFS(H2:J2,R2:T2,H2:J2,"?*")),"Match", "No match")

Thanks, as ever Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.