Match two columns in one formula

[randolphoralph]

I have defined a name range and titled it "Data" and trying to match B2 or C2 against the values in the name range "Data"

For example...

The formula should return "Yes" when

B2 does match value in "Data", but C2 does not

B2 does not match value in "Data", but C2 does

B2 and C2 both match

The formula should return "No" when

neither B2 and C2 have matching values in "Data"

Here is what I have so far, but I am not sure how to change the formula to include the 2nd match for C2.

=IF(ISERROR(MATCH(B2,Data,0)),"No","Yes")

 

[AlphaFrog]

Try something like this..

=IF(OR(COUNTIF(Data, B2)>0,COUNTIF(Data, C2)>0),"Yes", "No")

 

[T. Valko]

I have defined a name range and titled it "Data" and trying to match B2 or C2 against the values in the name range "Data"

For example...

The formula should return "Yes" when

B2 does match value in "Data", but C2 does not

B2 does not match value in "Data", but C2 does

B2 and C2 both match

The formula should return "No" when

neither B2 and C2 have matching values in "Data"

Here is what I have so far, but I am not sure how to change the formula to include the 2nd match for C2.

=IF(ISERROR(MATCH(B2,Data,0)),"No","Yes")

What is the actual range address for "Data"?

 

[randolphoralph]

AlphaFrog....your solution worked perfectly. Thank you.



T. Valko....the actual range address is Sheet1 A1:A780

 

[T. Valko]

T. Valko....the actual range address is Sheet1 A1:A780

OK, here are a couple of other ways...

=IF(SUMPRODUCT(COUNTIF(Data,B2:C2)),"Yes","No")

This one needs to be array entered**:

=IF(COUNT(MATCH(Data,B2:C2,0)),"Yes","No")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.