Check Row Matches a row in an array

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
Hi All

I have 2 workbooks and i want to check if a row matches another row in another book.

Lets say in Workbook1 you have the following


Workbook 1Workbook2
ABCDTYUO
WERT
ABCD

I have the following formula but its not working

{=AND(EXACT(A2:D2,[Workbook1.xlsx]Workbook2!$A$2:$D$4))}

Gives me a result of false

Just want to know if that exact row is within the array.

Can anyone help please
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,491
Perhaps something like this:

Book1 (version 1).xlsb
ABCDEFGHIJKLMNOPQRSTUV
1FALSE3zz
2
3abcdefghijk
4
5bbbdefghijk
6zyxwvutsrqp
7abcdefghizzk
8accccdefghijk
Sheet11
Cell Formulas
RangeFormula
A1A1=MAX(MMULT(--EXACT(A3:K3,L5:V8),{1;1;1;1;1;1;1;1;1;1;1}))=11
B1B1=IF(NOT(A1),MATCH(10,MMULT(--EXACT(A3:K3,L5:V8),{1;1;1;1;1;1;1;1;1;1;1}),0),"")
C1C1=INDEX(L5:V8,B1,MIN(IF(INDEX(L5:V8,B1,0)<>A3:K3,COLUMN(A3:K3)-COLUMN(A3)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas.


The B1 formula finds the offset into the table where there's a row that matches 10 out of 11 columns. Note in this example, row 4 also matches except for ccc. Then the C1 formula finds the different column. You will of course have to change the ranges to match your workbooks/sheets.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
Perhaps something like this:

Book1 (version 1).xlsb
ABCDEFGHIJKLMNOPQRSTUV
1FALSE3zz
2
3abcdefghijk
4
5bbbdefghijk
6zyxwvutsrqp
7abcdefghizzk
8accccdefghijk
Sheet11
Cell Formulas
RangeFormula
A1A1=MAX(MMULT(--EXACT(A3:K3,L5:V8),{1;1;1;1;1;1;1;1;1;1;1}))=11
B1B1=IF(NOT(A1),MATCH(10,MMULT(--EXACT(A3:K3,L5:V8),{1;1;1;1;1;1;1;1;1;1;1}),0),"")
C1C1=INDEX(L5:V8,B1,MIN(IF(INDEX(L5:V8,B1,0)<>A3:K3,COLUMN(A3:K3)-COLUMN(A3)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas.


The B1 formula finds the offset into the table where there's a row that matches 10 out of 11 columns. Note in this example, row 4 also matches except for ccc. Then the C1 formula finds the different column. You will of course have to change the ranges to match your workbooks/sheets.
Very clever

But looks like B1 is picking up the wrong column, it thinks its 5 when in fact the issue is on 11.


5GUCL15102/12/2005FH2R21/10/2020221/10/2020T
FALSE​
5​
AH2
5GUCL15102/12/2005FH4R21/10/2020421/10/2020T
TRUE​
#VALUE!
5GUCL15102/12/2005GH2R21/10/2020221/10/2020T
TRUE​
#VALUE!
5GUCL15102/12/2005GH4R21/10/2020421/10/2020T
TRUE​
#VALUE!
5GUCL15102/12/2005HH2R21/10/2020221/10/2020T
FALSE​
5​
AH2
5GUCL15102/12/2005HH4R21/10/2020421/10/2020T
TRUE​
#VALUE!
5GUCL15102/12/2005JH2R21/10/2020221/10/2020T
FALSE​
5​
AH2
5GUCL15102/12/2005JH4R21/10/2020421/10/2020T
TRUE​
#VALUE!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,491
I'm sorry, I can't tell what your source line is, or where your table is, or even if the formulas are correct. Consider using the XL2BB tool (there is a link in the response box, or my signature). It's easy to download and use, and makes it much easier to look at your data. At least I need to know what line you're comparing, what line in the table you think it should almost match (10 out of 11), and thus what column value should be shown.
 

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
I'm sorry, I can't tell what your source line is, or where your table is, or even if the formulas are correct. Consider using the XL2BB tool (there is a link in the response box, or my signature). It's easy to download and use, and makes it much easier to look at your data. At least I need to know what line you're comparing, what line in the table you think it should almost match (10 out of 11), and thus what column value should be shown.
Hi Eric

I thought i replied to this apologies, it worked perfect, it was me in the end lol

i do how ever have another question regarding this

Is there now a way to check if the row is in the range, but does not need to be in the same order, just as long as its in the row.

Example

Row to check

HatCatDogFish

Range


HatCanDogFish
BatDogFatTan
FistDogCatHat
BatCanFatTan

As you can see there is a match in row 3

Thought changing the exact for match might work, but did not =MAX(MMULT(MATCH(C2:F2,$I$2:$L$5),{1;1;1;1}))=4

TIA
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
3,953
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

ABCD
1
2ABCB
3
4Found?TRUE
5
6BDAE
7FDCA
8CBAB
9BCDE
FOUR
Cell Formulas
RangeFormula
B4B4=OR(MMULT(IFERROR(MATCH(A6:D9,A2:D2,),99),{1;1;1;1})=SUM(MATCH(A2:D2,A2:D2,)))

You may need to array-enter unless you have Excel 365.

Note that it's not case-sensitive. I think it will always work for 4 columns, but it definitely doesn't generalise to 5+ columns.

If you don't allow duplicates within a row, you can use the simpler:
=MAX(MMULT(COUNTIF(A2:D2,A6:D9),{1;1;1;1}))=4
 

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
ABCD
1
2ABCB
3
4Found?TRUE
5
6BDAE
7FDCA
8CBAB
9BCDE
FOUR
Cell Formulas
RangeFormula
B4B4=OR(MMULT(IFERROR(MATCH(A6:D9,A2:D2,),99),{1;1;1;1})=SUM(MATCH(A2:D2,A2:D2,)))

You may need to array-enter unless you have Excel 365.

Note that it's not case-sensitive. I think it will always work for 4 columns, but it definitely doesn't generalise to 5+ columns.

If you don't allow duplicates within a row, you can use the simpler:
=MAX(MMULT(COUNTIF(A2:D2,A6:D9),{1;1;1;1}))=4

Works perfect, still getting my head around MMULT
 

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75

ADVERTISEMENT

ABCD
1
2ABCB
3
4Found?TRUE
5
6BDAE
7FDCA
8CBAB
9BCDE
FOUR
Cell Formulas
RangeFormula
B4B4=OR(MMULT(IFERROR(MATCH(A6:D9,A2:D2,),99),{1;1;1;1})=SUM(MATCH(A2:D2,A2:D2,)))

You may need to array-enter unless you have Excel 365.

Note that it's not case-sensitive. I think it will always work for 4 columns, but it definitely doesn't generalise to 5+ columns.

If you don't allow duplicates within a row, you can use the simpler:
=MAX(MMULT(COUNTIF(A2:D2,A6:D9),{1;1;1;1}))=4
Tried to up scale it to the document and not working, getting #N/A

=OR(MMULT(IFERROR(MATCH(Sheet1!$A$2:$H$249861,A2:H2,),99),{1;1;1;1;1;1;1;1})=SUM(MATCH(A2:H2,A2:H2,)))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,491
I think this works:

Book1
ABCDEFGHIJKLMNOPQRSTUV
13
2
3abcdffghijk
4
5bbbdefghijk
6zyxwvutsrqp
7ajcfdfghibk
8accccdefghijk
Sheet8
Cell Formulas
RangeFormula
A1A1=MATCH(11,MMULT(--(COUNTIF(A3:K3,A3:K3)=COUNTIF(OFFSET(L5:V5,ROW(L5:L8)-ROW(L5),0),A3:K3)),{1;1;1;1;1;1;1;1;1;1;1}),0)


It'll return the row that matches, or an #N/A.
 

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
75
Tried to up scale it to the document and not working, getting #N/A

=OR(MMULT(IFERROR(MATCH(Sheet1!$A$2:$H$249861,A2:H2,),99),{1;1;1;1;1;1;1;1})=SUM(MATCH(A2:H2,A2:H2,)))
Sorted, every cell has to have a value
 

Watch MrExcel Video

Forum statistics

Threads
1,119,060
Messages
5,575,869
Members
412,689
Latest member
nhsmedic
Top