Using MATCH function to find an occurrence of names in a named range

Hi Guys,

I'm trying to find any occurrence of a name in either of two columns and return a 1 if it occurs. I have a named range that I have created with all the names that I want to identify.

Now I have tried to do this various ways with IF, IF and OR, and SUM functions with no luck - It keeps returning N/A every time and i'm sure that my logic is correct. I am now thinking it something to do with the match function that i'm using. If anyone could take a look at it and let me know where i'm going wrong it would be much appreciated.


I have included a sample document with data similar to what i'm working with. I have also populated it with the two solutions that I thought would work. Thanks in advance!

Originally Posted by daddylonglegs

MATCH returns a number if the name is found.....or an error otherwise, you need to deal with the error somehow, try either this version with MATCH

=IF(COUNT(MATCH(E2,Managers,0),MATCH(F2,Managers,0)),1,0)

or another way for the same result.....

=(SUMPRODUCT(COUNTIF(Managers,E2:F2))>0)+0

....or even

=(SUMPRODUCT((E2:F2=Managers)+0)>0)+0

The latter two rely on E2:F2 being a contiguous range - the last one also relies on E2:F2 being a horizontal range as opposed to Managers being a vertical range.....

Thanks daddylonglegs, appreciated the explanation too!

Or as you showed a few minutes ago DLL.

=IF(OR(COUNTIF(Managers,E2)>0,COUNTIF(Managers,F3)>0),1,0)