Is a value a member of an array?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
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,
 
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)
 
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.
 
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,
 
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,
 
Use conditional formatting to hide False.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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,
 
Back
Top