+ Reply to Thread
Results 1 to 5 of 5

[SOLVED] two arrays

  1. #1
    Jerry Kinder
    Guest

    [SOLVED] two arrays

    Hi,
    In col A througn F there are numbers about 150 rows down.
    In G1 through L1 there are numbers and these will change from time to time.
    G2 through L2 and down I want a formula that will find the numbers that
    are in A through F and put YES in the corresponding cell under G to L.
    So any cell in A:F that has one of the numbers in G:L the formula will
    put yes in the corresponding cell under G:L if the numbers is in G1:L1.


    A B C D E F G H I J
    K L
    Numbers 3 5 12 16 34 27
    38 40 48 52 56 46
    34 32 33 43 53 9 Yes
    8 15 16 24 38 19
    13 15 16 32 41 22
    23 36 27 41 42 21 Yes
    11 27 32 48 52 28
    5 7 12 16 45 19 Yes Yes
    24 31 33 46 50 7
    16 17 18 30 37 27 Yes Yes
    2 13 16 37 44 32


    Hope this makes sense.
    Thanks,
    Jerry



  2. #2
    ChelseaWarren
    Guest

    RE: two arrays

    Do you want the rows in A through F to match the first row in H through L?

    If so, and I understand you correctly, try putting this formula in G2 and
    copying it down and across: =IF(H$1=A2,"YES","")



    "Jerry Kinder" wrote:

    > Hi,
    > In col A througn F there are numbers about 150 rows down.
    > In G1 through L1 there are numbers and these will change from time to time.
    > G2 through L2 and down I want a formula that will find the numbers that
    > are in A through F and put YES in the corresponding cell under G to L.
    > So any cell in A:F that has one of the numbers in G:L the formula will
    > put yes in the corresponding cell under G:L if the numbers is in G1:L1.
    >
    >
    > A B C D E F G H I J
    > K L
    > Numbers 3 5 12 16 34 27
    > 38 40 48 52 56 46
    > 34 32 33 43 53 9 Yes
    > 8 15 16 24 38 19
    > 13 15 16 32 41 22
    > 23 36 27 41 42 21 Yes
    > 11 27 32 48 52 28
    > 5 7 12 16 45 19 Yes Yes
    > 24 31 33 46 50 7
    > 16 17 18 30 37 27 Yes Yes
    > 2 13 16 37 44 32
    >
    >
    > Hope this makes sense.
    > Thanks,
    > Jerry
    >
    >
    >


  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    In G2 copied across and down

    =IF(ISNUMBER(MATCH(G$1,$A2:$F2,0)),"yes","")

  4. #4
    Jerry Kinder
    Guest

    Re: two arrays

    THANKS, it works great!
    Jerry

    "daddylonglegs" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > In G2 copied across and down
    >
    > =IF(ISNUMBER(MATCH(G$1,$A2:$F2,0)),"yes","")
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile:

    http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=516554
    >




  5. #5
    Max
    Guest

    Re: two arrays

    Just to extend it a little further ..

    If we want to count the number of numbers within cols A to F (in row2 down)
    which match the reference set of numbers in G1:L1 (eg: as in checking lotto
    tickets against results)

    we could put in say, M2:
    =SUMPRODUCT(--ISNUMBER(MATCH(A2:F2,G$1:L$1,0)))
    and copy M2 down to return the counts

    [The numbers in cols A to F, and those in the reference set
    can be in any order, need not be sorted]
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1