# Thread: Finding matching values (WinME,OfficeXP)

1. ## Finding matching values (WinME,OfficeXP)

I can't figure out how to solve a simple array problem without using nested IF statements.

Suppose you have an array of six integer values (six lotto numbers drawn), and you have a series of five more arrays each containing six integer values (five lotto plays). You want to compare each drawn number against each group of played numbers and tally how many matches you have. The end result should be in a range from zero or blank to six.

What Excel functions, if any, will do this? What would a VBA routine look like?

Enquiring minds want to know. Remember, I've solved this using nested IF statements but thought there ought to be a niftier way to do it.

2. ## Re: Finding matching values (WinME,OfficeXP)

If your six drawn number sare in A1 to F1, and the numbers you want to check are in A2:F2 then the following formual s hould return the number of matches : <pre><big>
= SUM(IF(ISNA(MATCH(A2:F2,A1:F1,0)),0,1))</big></pre>

The above formula should be array entered, i.e. press Ctrl-Shift and Enter to apply it.

Andrew C

3. ## Re: Finding matching values (WinME,OfficeXP)

Works like a Champ

Thanks, Andrew.

#### Posting Permissions

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