Results 1 to 8 of 8

Thread: AND function for an array

  1. #1

    Exclamation AND function for an array



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    I have a spreadsheet with two worksheets that needs to have the information compared. Data is in one worksheet starts from G2 and in the other worksheet it starts from C2 & it should match as the first condition. The second condition is that O2 and E2 also match but because the data is not in order it needs to look within an array A2:A706 to Y2:Y706.

    I can't figure out how to use the AND function within an array to make it work. It may be the case that I need VBA to make it work?

    Once worksheet has 119 rows and the other has 705 with data within them.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    It depends on whether the conditions have to be met on the same row or not.

    If the matches have to occur in the same rows, then use:

    =COUNTIF(SHEET2!$G2$2:$G$706,C2,SHEET2!$O$2:$O$706,E2)>0

    copied down

    If the matches don't have to occur in same row, then:

    =AND(ISNUMBER(MATCH(C2,SHEET2!$G2$2:$G$706,0)),ISNUMBER(MATCH(E2,SHEET2!$O$2:$O$706,0)))

    copied down

    Adjust ranges and sheetnames to suit before copying down.


  3. #3
    Hi Thanks for your response, I am slightly confused with the countif function because excel doesn't recognise that as a formula when I copy in my data. Also I think my explanation isn't clear on the data arrangement.

    Worksheet 1 has 706 rows for A to Y
    Worksheet 2 has 119 rows from A to I

    I want to essentially do something similar to a vlookup but with a lookup value which is two cells i.e G2 and O2 from Worksheet 1
    In Worksheet 2 I need to find the same values found in G2 & O2 but in C2 and E2 because I will know its the exact match.
    If I used one condition such as G2 and C2 which is an account number it would match but the additional check would confirm that it is a true match by ensuring the values of 02 and C2 appear in the same rows as the account number which proves its the correct data entry

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Sorry that should have been COUNTIFS

    e.g.

    =COUNTIFS(SHEET2!$G2$2:$G$706,C2,SHEET2!$O$2:$O$706,E2)>0

    copied down.

    This checks that the values in C2 and E2 of Worsheet 1, matches column G and column O values, respectively in Sheet2 (within same row).


  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    779
    Articles
    0
    Excel Version
    2010
    @NBVC
    I think there's a typo that will invalidate your expression:

    Sheet2!$G2$2 should be Sheet2!$G$2 I think.

    Hercules

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Thanks Hercules, you are correct.

    =COUNTIFS(SHEET2!$G$2:$G$706,C2,SHEET2!$O$2:$O$706,E2)>0


  7. #7
    Thank you for your responses, this function is not available in 2003? That is the version that I have. Is there another way of doing it with the version of Excel that I have?

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    779
    Articles
    0
    Excel Version
    2010
    The COUNTIFS function was introduced in Excel 2007. Although you say that you are looking for matches in row 2 on both worksheets, I think this might be wrong as both sheets have different (populated) row counts. Im assuming that you mean that the keys in Cols C and E in Sheet2 should be used to match Cols G and O in Sheet1, but a match needs to be sought in any of Sheet1's 706 rows. Assuming the formula is entered in Sheet2 starting in A2, the following will return "Match" (if copied down 119 rows) against any matches found in Sheet1:
    =IF(SUMPRODUCT((G2=Sheet1!$C$2:$C$706)*(O2=Sheet1!$E$2:$E$706))>0,"Match","")

    HTH

Posting Permissions

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