+ Reply to Thread
Results 1 to 4 of 4

Index array formula?

  1. #1
    Registered User
    Join Date
    04-10-2009
    Location
    Colorado
    MS-Off Ver
    Excel 2016
    Posts
    30

    Index array formula?

    I had someone help me with this formula already but I can't remember the exact formulation. I have a string of numbers (2 columns) that I am trying to compare to each other to find numbers that don't match to any number in the other column. I would like for the formula to give me a "1" if the numbers match and a "0" if the formula doesn't find a match, i.e.:

    WORD ACCESS
    247604 1 247604
    290312 1 290312
    296787 1 296787
    297038 0 297052
    312471 1 312471
    320959 1 320959

    I would like to compare the WORD list to the ACCESS and vice versa. I know it's a pretty simple formula but just not knowledgeable enough in Excel.
    Last edited by kdestef1; 04-10-2009 at 12:28 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index array formula?

    i dont think you can do that like that
    i think you need 2 seperate countifs

    =IF(COUNTIF($C$1:$C$6,A1),1,0) and =IF(COUNTIF($A$1:$A$6,C1),1,0) if you combine wiith OR() it would give false results as if a number in column a1 say is in column c that would return 1 but if the number in c1 wasnt in col a it would still return 1
    Last edited by martindwilson; 04-10-2009 at 12:42 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    01-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    67

    Re: Index array formula?

    Quote Originally Posted by kdestef1 View Post
    I had someone help me with this formula already but I can't remember the exact formulation. I have a string of numbers (2 columns) that I am trying to compare to each other to find numbers that don't match to any number in the other column. I would like for the formula to give me a "1" if the numbers match and a "0" if the formula doesn't find a match, i.e.:

    WORD ACCESS
    247604 1 247604
    290312 1 290312
    296787 1 296787
    297038 0 297052
    312471 1 312471
    320959 1 320959

    I would like to compare the WORD list to the ACCESS and vice versa. I know it's a pretty simple formula but just not knowledgeable enough in Excel.
    Try:
    =N(A1=C1)
    if you are comparing number side by side

    or
    =N(AND(ISNUMBER(MATCH(A1,$C$1:$C$4,0)),ISNUMBER(MATCH(C1,$A$1:$A$4,0))))
    to know if both numbers exists in the cross lists

    or
    =N(OR(ISNUMBER(MATCH(A1,$C$1:$C$4,0)),ISNUMBER(MATCH(C1,$A$1:$A$4,0))))
    to know if atleast one of the number exists in the cross list.

  4. #4
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: Index array formula?

    place this in B2

    =IF(A2=C2,1,0)

    this assume
    1) comparison side by side
    2) that both list will sort and
    3) thar they have the same range

    otherwise, the result will be incorrect

    HTH

    cheers, francis

+ 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