AND function for an array

rue

New member
Joined
Aug 5, 2013
Messages
3
Reaction score
0
Points
0
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

rue

New member
Joined
Aug 5, 2013
Messages
3
Reaction score
0
Points
0
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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).
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
@NBVC
I think there's a typo that will invalidate your expression:

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

Hercules
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Thanks Hercules, you are correct.

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

rue

New member
Joined
Aug 5, 2013
Messages
3
Reaction score
0
Points
0
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?
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 
Top