 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Is a value a member of an array?
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, Try this in C1: =COUNTIF(A1:A300,B1)>0  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- >I need to test if a value is a member of an array. For example A1:A300 is >an [quoted text clipped - 6 lines] > > 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, > Try this in C1: > [quoted text clipped - 10 lines] > > > > Thanks, Just use =SUMPRODUCT(--($A$1:$A$300=B1),--($A$1:$A$300<>""))>0 SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > 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. [quoted text clipped - 21 lines] > > > > > > Thanks, 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, > Just use > [quoted text clipped - 32 lines] > > > > > > > > Thanks, Use conditional formatting to hide False. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > 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. [quoted text clipped - 37 lines] > > > > > > > > > > Thanks, 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. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > 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. [quoted text clipped - 19 lines] >> > >> > Thanks, 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, > 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 [quoted text clipped - 24 lines] > >> > > >> > Thanks, Quite *different* from your OP, isn't it? SignatureRegards, 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, "RagDyer" wrote: > 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 [quoted text clipped - 30 lines] > >> > > >> > Thanks,
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.