# Check Row Matches a row in an array

#### Cooki

##### Board Regular
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 1 Workbook2 A B C D T Y U O W E R T A B C D

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
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.

### Excel Facts

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

#### Cooki

##### Board Regular
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.

 5 GUCL 151 02/12/2005 FH2 R 21/10/2020 2 21/10/2020 T FALSE​ 5​ AH2 5 GUCL 151 02/12/2005 FH4 R 21/10/2020 4 21/10/2020 T TRUE​ #VALUE! 5 GUCL 151 02/12/2005 GH2 R 21/10/2020 2 21/10/2020 T TRUE​ #VALUE! 5 GUCL 151 02/12/2005 GH4 R 21/10/2020 4 21/10/2020 T TRUE​ #VALUE! 5 GUCL 151 02/12/2005 HH2 R 21/10/2020 2 21/10/2020 T FALSE​ 5​ AH2 5 GUCL 151 02/12/2005 HH4 R 21/10/2020 4 21/10/2020 T TRUE​ #VALUE! 5 GUCL 151 02/12/2005 JH2 R 21/10/2020 2 21/10/2020 T FALSE​ 5​ AH2 5 GUCL 151 02/12/2005 JH4 R 21/10/2020 4 21/10/2020 T TRUE​ #VALUE!

#### Eric W

##### MrExcel MVP
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
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

 Hat Cat Dog Fish

Range

 Hat Can Dog Fish Bat Dog Fat Tan Fist Dog Cat Hat Bat Can Fat Tan

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

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
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

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
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
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

Replies
2
Views
108
Replies
3
Views
197
Replies
9
Views
159
Replies
5
Views
82
Replies
24
Views
1K

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