Check Row Matches a row in an array

[Cooki]

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]

Perhaps something like this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	FALSE	3	zz
2
3	a	b	c	d	e	f	g	h	i	j	k
4
5												b	b	b	d	e	f	g	h	i	j	k
6												z	y	x	w	v	u	t	s	r	q	p
7												a	b	c	d	e	f	g	h	i	zz	k
8												a	ccc	c	d	e	f	g	h	i	j	k
Sheet11

Cell Formulas
Range	Formula
A1	A1	=MAX(MMULT(--EXACT(A3:K3,L5:V8),{1;1;1;1;1;1;1;1;1;1;1}))=11
B1	B1	=IF(NOT(A1),MATCH(10,MMULT(--EXACT(A3:K3,L5:V8),{1;1;1;1;1;1;1;1;1;1;1}),0),"")
C1	C1	=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.

 

[Cooki]

Perhaps something like this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	FALSE	3	zz
2
3	a	b	c	d	e	f	g	h	i	j	k
4
5												b	b	b	d	e	f	g	h	i	j	k
6												z	y	x	w	v	u	t	s	r	q	p
7												a	b	c	d	e	f	g	h	i	zz	k
8												a	ccc	c	d	e	f	g	h	i	j	k
Sheet11

Cell Formulas
Range	Formula
A1	A1	=MAX(MMULT(--EXACT(A3:K3,L5:V8),{1;1;1;1;1;1;1;1;1;1;1}))=11
B1	B1	=IF(NOT(A1),MATCH(10,MMULT(--EXACT(A3:K3,L5:V8),{1;1;1;1;1;1;1;1;1;1;1}),0),"")
C1	C1	=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]

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]

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]

	A	B	C	D
1
2	A	B	C	B
3
4	Found?	TRUE
5
6	B	D	A	E
7	F	D	C	A
8	C	B	A	B
9	B	C	D	E
FOUR

Cell Formulas
Range	Formula
B4	B4	=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]

	A	B	C	D
1
2	A	B	C	B
3
4	Found?	TRUE
5
6	B	D	A	E
7	F	D	C	A
8	C	B	A	B
9	B	C	D	E
FOUR

Cell Formulas
Range	Formula
B4	B4	=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]

	A	B	C	D
1
2	A	B	C	B
3
4	Found?	TRUE
5
6	B	D	A	E
7	F	D	C	A
8	C	B	A	B
9	B	C	D	E
FOUR

Cell Formulas
Range	Formula
B4	B4	=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]

I think this works:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	3
2
3	a	b	c	d	f	f	g	h	i	j	k
4
5												b	b	b	d	e	f	g	h	i	j	k
6												z	y	x	w	v	u	t	s	r	q	p
7												a	j	c	f	d	f	g	h	i	b	k
8												a	ccc	c	d	e	f	g	h	i	j	k
Sheet8

Cell Formulas
Range	Formula
A1	A1	=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]

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