Find matches by given criteria and divide by equal values up to 100%

[K0st4din]

Hello, everyone,
since 2 month I have been going crazy, looking for a solution, how to automate a rather large table so that I can finish it faster, because my eyes are going to hell.
I have a very large table, in which on the right I have given names (in the example) from IVAN IVANOV 1 to IVAN IVANOV 35 - AK - BS.
From all these names in column BU, I have specifically selected names.
On the left of the same table, I have horizontally these same names from IVAN IVANOV 1 to IVAN IVANOV 35 - A:AI .
What I do is according to the selected names in column BU, start from A1 first, then A3, then B2 (as a final option and selection), look them up in the table on the right. Then on the same row, but already in the table on the left, if the name from BU - corresponds first to A1, then to A3 and finally to B2, to divide equally by the numbers to get 100% in column AJ (and the corresponding row ).
Apparently I can't handle it, because I tried with excel index match multiple criteria, SUM, SUMIF, but things don't work out for me.
I am asking for some help because I am processing over 2000 rows and this search and compare is totally killing me.
I will attach a sample table and I remain available if you have any questions, because for me personally, you may be confused somewhere in the very clarity of my problem.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU
1
2
3
4
5
6	PRODUCT VIS %
7	IVAN IVANOV 1	IVAN IVANOV 2	IVAN IVANOV 3	IVAN IVANOV 4	IVAN IVANOV 5	IVAN IVANOV 6	IVAN IVANOV 7	IVAN IVANOV 8	IVAN IVANOV 9	IVAN IVANOV 10	IVAN IVANOV 11	IVAN IVANOV 12	IVAN IVANOV 13	IVAN IVANOV 14	IVAN IVANOV 15	IVAN IVANOV 16	IVAN IVANOV 17	IVAN IVANOV 18	IVAN IVANOV 19	IVAN IVANOV 20	IVAN IVANOV 21	IVAN IVANOV 22	IVAN IVANOV 23	IVAN IVANOV 24	IVAN IVANOV 25	IVAN IVANOV 26	IVAN IVANOV 27	IVAN IVANOV 28	IVAN IVANOV 29	IVAN IVANOV 30	IVAN IVANOV 31	IVAN IVANOV 32	IVAN IVANOV 33	IVAN IVANOV 34	IVAN IVANOV 35	100%	IVAN IVANOV 1	IVAN IVANOV 2	IVAN IVANOV 3	IVAN IVANOV 4	IVAN IVANOV 5	IVAN IVANOV 6	IVAN IVANOV 7	IVAN IVANOV 8	IVAN IVANOV 9	IVAN IVANOV 10	IVAN IVANOV 11	IVAN IVANOV 12	IVAN IVANOV 13	IVAN IVANOV 14	IVAN IVANOV 15	IVAN IVANOV 16	IVAN IVANOV 17	IVAN IVANOV 18	IVAN IVANOV 19	IVAN IVANOV 20	IVAN IVANOV 21	IVAN IVANOV 22	IVAN IVANOV 23	IVAN IVANOV 24	IVAN IVANOV 25	IVAN IVANOV 26	IVAN IVANOV 27	IVAN IVANOV 28	IVAN IVANOV 29	IVAN IVANOV 30	IVAN IVANOV 31	IVAN IVANOV 32	IVAN IVANOV 33	IVAN IVANOV 34	IVAN IVANOV 35		SELECT PRODUCT
8																																				0								B2					B2			B2								B2			B2	B2	B3		B2	B2	D1	B2			IVAN IVANOV 5
9																																				0				B2	B2			B2	B2	B2					C2	B2			B2			B2															IVAN IVANOV 10
10	1																																			1	B2	B2	B2	B2	C2	C2	B1	C2	B2		B2				B2	A3				A3	B2	B2		B2			B2	B1		B2	A3	A3		B2			IVAN IVANOV 11
11	1	1	1	1	1		1	1	1	1	1			1	1	1			1	1	1	1					1	1	1	1						21	A1	B2	B2	A1	A1	C2	A1	A1	A1	A3	B2			A1	A1	A1			B2	B2	B2	A3					A1	A1	A1	A1							IVAN IVANOV 12
12																																				0																																					IVAN IVANOV 27
13	0		0		0		0	0	0	0	0	0		0	0	0	0		0	0	0	0		0	0	0	0	0	0	0	0	0	0	0		######	B2		A1		A1		A1	A3	A3	B2	A3	B2		B2	A1	A1	A3		A3	A3	A3	A3		B2	B2	B2	B2	B2	B2	B2	B2	A3	B2	A3			……….TO THE END
14					A1					B2	A1	A1															A3
15					20					20	20	20															20									100	A3	B2	B2	B2	A1	A3	A3	A3	C2	B2	A1	A1	A3	B2	A1	A3	A3		A3	A3	A3	A3		A1	B2	A3	A3	C1	A3	B2	B2	A3	A3	A3	B2
16					B2																						B2
17					No, in all info from IVAN IVANOV 1 to IVAN IVANOV 35, MISSING A1 or A3, from locking product in BU																						No, in all info from IVAN IVANOV 1 to IVAN IVANOV 35, MISSING A1 or A3, from locking product in BU									0					B2			B2							B2					B2		B2		B2	B2	B2	B2	B2	B2	B2	A1	B2	B2	B2	B2
18																																				0	B2			B2	B2	B2		B2	B2
19					A3					B2	B2	A1															A3									0	B1	B1	B1	B1	B1	B1	B1	C2	C2	B2		B1			B2	B2						B2		B2			C2	B2	C2		B2	B2	B2	B2
20					20					20	20	20															20									100	B1		B3		A3	C2	B2	A3	A3	B2	B2	A1		A3	A3	A3	A3		C2	A3	A3	A3		A3	B2		A3		A3	A3	A3	A3	A3		A1
21					A1					A3	B2																A1									0	B2	B2	C2	B2	C2	C2	C2	C2	C2		C2	B2	C2											C1	B2		B2	C1	B2	B2		C1	B2	B2	B1
22					25					25	25																25									100	A1	B2	B2	A1	A1	C2	A1	A1	A1	A3	B2			A1	A1	A1			B2	B2	B2	A3					A1	A1	A1	A1
23																																				0	B2	B2	B2	B2	B1	B1	B1	B2	B2	B2	B2				B2	B2						B2
24					A3					B2	A3	B2															B2									0	B2	B2	B2	B2	B2	B2	B2	A3	B2	B2	B2	B2	B2	B2	B2	A3	A3	A3	B2	A3	A1	B2		B2	A3	B2	B2	B2	B2	A3	A3	A3	A3	A3	B2
25					20					20	20	20															20									100	B2	B2	C2	B2	A3	B2	B2	A1	B2	B2	A3	B2	B2	B2	A3	A3	A3		A3	A3	A3	B2		B2	B2	B3	B2	B2	B2	A3	A3	A3	A3	B2	A3
26					B2					B2	B2	B1															B2
27					25					25	25																25									100	B2	C2	B2	B2	B2	B2	B2	B2	B2	B2	B2	B1	B2	B2	B2	B2			C2	B2		B2		B2	B2	B3	B2	B2	B2	B2	B2	B2	B2	B2	B2
28					A1					A3	A3	B2															A1									0	B2	B2	B2	B2	C2	C2	C2	C2	C2	B2	C2	C2	B2		B2	B2						B2		C1			B2	C1	B2			B2	B2	B2	B2		……….TO THE END
29					20					20	20	20															20									100	A3	A3	B2		A1	B2	B2	A3	B2	A3	A3	B2	B2	A3	A1	A3	A3		A3	A3	B2	A3		C1	A3	B2	A1	C1	C1	A3	A1	A3	B2	B2	B2
30																																				0	A3	B2	B2	A3	A1	C2	B2	A1	A3	A3	A1	B2	B2	A1	A3	A3			A3	A3	A3	B2	B2	B2	A1	B2	B2	B2	B2	A1	A3	A1	B2	B2	B2
Sheet2

Cell Formulas
Range	Formula
A11:AI11,A8:A10	A8	=IF(AK8="B1",1,IF(AK8="A1",1,IF(AK8="A3",1,IF(AK8="B2",1,IF(AK8="B2",1,"")))))
AJ27:AJ30,AJ17:AJ25,AJ15,AJ8:AJ13	AJ8	=SUM(A8:AI8)
A12:AI12	A12	=IF(AK12<>"",AK12/SUM($A$12:$AI$12),"")
A13:AI13	A13	=IF(AK13<>"",AK13/SUM($A$13:$AI$13),"")

 

[K0st4din]

Let me add one more thing:
The example with the division being equal is according to how many A1, A3 and B2 there are for the given row. The idea is that if I have 5, I divide them 100 / 5 and make each marked 20.
Since, in the example, I have forgotten test formulas, they do not play any role.
15,17,20,22,25,27,29 - these lines are an example of what should be obtained. The description of A1, A3, B2 above each of these lines (the previous one) is only and only, for your convenience, to be able to see, if the way and the formula are found, that there below should be the values, because they correspond in a real case of the truth.
I really hope someone can help me.
Thanks in advance and stay healthy!

 

[K0st4din]

Hello, anyone with any idea or is there any ambiguity in my query!? I keep looking for some solution on my own, but I can't manage. If you have any questions, to explain more and more, I'm on the line. Once again, thank you very much!

 

[K0st4din]

Friends, does anyone have any idea how things can turn out?
I can't believe there isn't some solution.