# Thread: Matching values in a range (97)

1. ## 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?

2. ## 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

3. ## 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>

4. ## Re: excel (97)

<P ID="edit" class=small>(Edited by sdckapr on 06-Oct-03 15:08. Add PS)</P>Does this array formula do what you want? (confirm with ctrl-shift-enter).

=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 1-2 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.

5. ## 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 "ctrl-shift-enter" to enter an expression. What does it do? (I see that curly brackets do result.)

Thanks.

6. ## 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.

7. ## Re: excel (97)

It is for entering ARRAY formulas.

For a good Intro on array formulas, see Chip Pearson's Site

Steve

8. ## 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...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•