Results 1 to 8 of 8
Thread: Matching values in a range (97)

20031006, 17:29 #1
 Join Date
 Aug 2003
 Location
 Voorhees, New Jersey
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel (97)
Thanks for the answer. It works for what I'm trying to do!
Is there any function to compare two arrays (say of same dimension) and call out the number of matches?
For instance I have two lists of ten numbers each and I want to know how many of the entries are in both lists?

20031006, 17:45 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel (97)
This will yield TRUE if the value in cell A1 occurs in the array from B1:B25. If it does NOT it returns FALSE. Change as appropriate.
=isnumber(Match(a1,B1:B25,0))
Steve

20031006, 18:06 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel (97)
Are the two lists in the same order so that matching values are in coresponding cells? If so, and if the values are in A1:A12 and B1:B12, then the following array formula (hold down Shift+Ctrl when you press the Enter key to enter the formula) will do that:
<pre>=SUM((A1:A12=B1:B12)*1)
</pre>
Legare Coleman

20031006, 18:08 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel (97)
<P ID="edit" class=small>(Edited by sdckapr on 06Oct03 15:08. Add PS)</P>Does this array formula do what you want? (confirm with ctrlshiftenter).
=SUM(IF(COUNTIF(A1:A12,C1:C12)>0,1))
Change ranges as appropriate.
I assume there are NO dupes in array C. If there are you will get multiple hits, even if there is one element of it in array A. For example if a1: A12 is numbers 12 and c1:c12 is all 1's it will find 12 matches since it each 1 is in the list. If there are some constraints of this sort, what are they?
Steve
Check out Chip Pearson's site for other info on duplicates and unique items in ranges.

20031006, 18:33 #5
 Join Date
 Aug 2003
 Location
 Voorhees, New Jersey
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel (97)
Neither array has repeating elements.
The formula you gave me does the trick!
Pardon my ignorance, but that's the 1st time I ever did a "ctrlshiftenter" to enter an expression. What does it do? (I see that curly brackets do result.)
Thanks.

20031006, 18:35 #6
 Join Date
 Aug 2003
 Location
 Voorhees, New Jersey
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel (97)
No such luck, but I'm thinking I ought ot be able to find a use for your formula now that I have it.
I'll keep it in my excel "folder".
Thanks.

20031006, 18:47 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel (97)
It is for entering ARRAY formulas.
For a good Intro on array formulas, see Chip Pearson's Site
Steve

20031006, 18:52 #8
 Join Date
 Aug 2003
 Location
 Voorhees, New Jersey
 Posts
 200
 Thanks
 0
 Thanked 0 Times in 0 Posts
Matching values in a range (97)
Subject of this thread edited by HansV to make it more descriptive
I'm looking for an excel function to report whether a given cell value occurs in an array. Any help would be appreciated...