Match a minimum of 3 and a max of 5 numbers for each row and add the number of cells containing the match displaying in G2 along each row

[devincentism]

I would like this in VBA or Formula. I am using MS 2021 Excel.

Trying to get Columns B thru F is the numbers assigned to each name in (My Main List) down 5 rows. Rows can be longer but in this example I have it at 5. Columns I thru M I have numbers entered (the entry list) down 10 rows but can be longer. I need see how many matches in each row of of my main list (B2:F2) matches all the cells along each row in my entry list (I2:M2) and Matching at least 3 out of 5 numbers.

Then each match it finds it increments and Displays totals at column G2:G6 and down along each row it matches.

I uploaded an image. In this example this is what I want my totals to be in G2:G6. Any help would be appreciated!

 

[Anonymous1378]

Try this in G2:

=SUM(IF(MMULT(SIGN(IFERROR(XMATCH($I$2:$M$10,B2:F2),0)),SEQUENCE(COLUMNS($I$2:$M$10),,,0))>=3,1))

 

[awoohaw]

Here is another try at it. But, I do not get your expected results. Can you recheck what your expectations are. I am unsure why you want to read accross the second matrix instead of down, but I have offered two solutions, one down, and one accross:

Mr excel questions 66.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	looking down the column
2	A	B	C	D	E	F	G	H	I	J	K	L	M
3	row1	16	21	29	32	38	3		29	32	38	4	5
4	row2	1	3	18	4	6	0		1	2	3	4	6
5	row3	10	14	19	23	25	2		18	19	23	21	35
6	row4	18	21	24	32	35	1		1	21	24	20	35
7	row5	1	7	22	23	35	3		16	32	3	38	5
8									1	2	3	4	10
9				Expected:			3		29	2	38	4	32
10							3		1	7	23	22	35
11							0		1	2	3	4	6
12							2
13							1
14	looking across row
15	A	B	C	D	E	F	G	H	I	J	K	L	M
16	row1	16	21	29	32	38	3		29	32	38	4	5
17	row2	1	3	18	4	6	4		1	2	3	4	6
18	row3	10	14	19	23	25	2		18	19	23	21	35
19	row4	18	21	24	32	35	3		1	21	24	20	35
20	row5	1	7	22	23	35	0		16	32	3	38	5
21									1	2	3	4	10
22				Expected:			3		29	2	38	4	32
23							3		1	7	23	22	35
24							0		1	2	3	4	6
25							2
26							1
devinecentism

Cell Formulas
Range	Formula
G3:G7	G3	=SUM(--($B3:$F3=INDEX($I$3:$M$11,,ROW(G3)-2)))
G16:G20	G16	=SUM(--($B3:$F3=TRANSPOSE(INDEX($I$3:$M$11,ROW(G16)-15,))))

 

[devincentism]

=SUM(IF(MMULT(SIGN(IFERROR(XMATCH($I$2:$M$10,B2:F2),0)),SEQUENCE(COLUMNS($I$2:$M$10),,,0))>=3,1))

Fantastic! That is what I needed. Thankyou

 

[smozgur]

Fantastic! That is what I needed. Thankyou

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.