Validate range of values against another range of values in Excel

Question

Similar to how CountIf validates a single value against a range of values (or, CountIfs validating a range of values against criteria), is there a way to validate a range of values against another range of values?

EX: First Range (R1) a1:a5 contains a,b,c,d,e

Second Range (R2) b1:b contains a,b,c

Upon checking whether R1 values are in R2, the outcome is FALSE due to 'd' and 'e' not in R2. Just wondering if there's already a function, or is there an easy way to build one. Thanks, J.

Answer 1

You can cook up a formula that will do this. For example, you can use SUMPRODUCT to check an array of conditions for COUNTIF and tally how many find a match. Then, compare that tally to the count of conditions you were searching for.

=SUMPRODUCT(--(COUNTIF(B1:B5,A1:A5)>0))=COUNTA(A1:A5)

A walk-through of the evaluation of this formula with the sample data you provided:

1. =SUMPRODUCT(--(COUNTIF(B1:B5,{a,b,c,d,e})>0))=COUNTA(A1:A5)
2. =SUMPRODUCT(--({1,1,1,0,0}>0))=COUNTA(A1:A5)
3. =SUMPRODUCT(--({TRUE,TRUE,TRUE,FALSE,FALSE}))=COUNTA(A1:A5)
4. =SUMPRODUCT({1,1,1,0,0})=COUNTA(A1:A5)
5. =3=COUNTA(A1:A5)
6. =3=5
7. =FALSE