Is a value a member of an array?

[Guest]

I need to test if a value is a member of an array. For example A1:A300 is an
array containing data in no particular order. B1 is a value that may or may
not be one of the entries in column A. In C1, I would like TRUE if B1 is a
member of A1:300 and false if B1 is not. I can't find an easy way to do this.
Any help is appreciated.

Thanks,

 

[RagDyer]

Try this in C1:

=COUNTIF(A1:A300,B1)>0

 

[Guest]

That's cool, but it didn't handle my "" values that I've got all over the
place. Here's what I'm using now. It's ugly, but it works.

=IF(SUMPRODUCT(--($A$1:$A$300=B1),--($A$1:$A$300<>""))>0,TRUE,"")

Thanks,

 

[Bob Phillips]

Just use

=SUMPRODUCT(--($A$1:$A$300=B1),--($A$1:$A$300<>""))>0

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[RagDyer]

I'm glad you've got a working formula, but as a point of information, I
don't follow what you mean by saying that the Countif() *didn't handle* your
nulls ( "" ).
Could you describe what returns you got when you tried the Countif()
formula.

 

[Guest]

I'm testing all of column B, not just B1. Say A50="" and B2="", C2=TRUE with
your formula, and for what I'm doing, I wanted it "" again.

Regards,

 

[Guest]

This works. Thanks. It turns out I wanted TRUE or "" instead of TRUE or
FALSE, but this is 1 step simpliar. I'm sure I'll sure it in the future.

Thanks,

 

[Bob Phillips]

Use conditional formatting to hide False.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[RagDyeR]

Quite *different* from your OP, isn't it?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

I'm testing all of column B, not just B1. Say A50="" and B2="", C2=TRUE with
your formula, and for what I'm doing, I wanted it "" again.

Regards,