Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    Texas, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •